Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    31

    Unanswered: Getting row data based on combo of Vlookup and IF?

    I have a large spread sheet which contains some information related to upcoming sales orders.

    This is a continuous file. Column A is the data column. Column B is the customer name column. Every day the date is incremented by 1, and the customer names are re-pasted in next to the new dates.

    I would like to get some information out of column D and E, which each contain different sales info, and place them into a master summary sheet.

    The summary sheet has the current date in it's own cell. It also has the customer name. My thought is that I should be able to get to column D and E data but that requires that two criteria are met, Column A need to be matched up to the date on the summary AND Column B needs to be matched to the customer cell on the summary. Once that happens, then I can return Column D value, or any value for that matter.

    The idea is that the summary sheet only display the most recent values for each customer and that the date on the summary combined with customer allows for a lookup to get the the most recent data for that customer on the master sheet which contains the daily values for the last 3 months.

    The issue is, I cannot seem to do it with IF's and I am stuck as to which real function I should use.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. You can use a hidden column (C) with concatenation on Data worksheet with this formula:

    =A2&B2

    Then name the range (using dynamic named range formula)- MyLookup

    =OFFSET(Data!$A:$1,0,0,COUNTA(Data!$A:$A),1)

    Then you can name other ranges off this using this formula; for instance column D, named range could be RngD,

    =OFFSET(MyLookup,0,1)

    etc.

    Then do a concatenation on the Summary worksheet as well, and then do a lookup formula based on that to the MyLookup named range.

    Hope this points you in the right direction. If you want mor,e perhaps post a sample.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Mar 2004
    Posts
    31
    Quote Originally Posted by shades
    Howdy. You can use a hidden column (C) with concatenation on Data worksheet with this formula:

    Then name the range (using dynamic named range formula)- MyLookup

    =OFFSET(Data!$A:$1,0,0,COUNTA(Data!$A:$A),1)
    I got stuck here, but that more likely because I realized that I typed the name of the sheets not really as they are.

    Attached is a zip file with the spreadsheet. The sheet SUMM reads needs to get its info from the sheet Cust_Serv. I Labeled the columns that need the summ data with a HELP above them on the SUMM sheet.
    Attached Files Attached Files

  4. #4
    Join Date
    Mar 2004
    Posts
    31
    I figured it finally. I got stuck on the offset, but it turned out not to be necessary for this sheet. Maybe it was less efficient, but the Conc field was all I needed on both sheets, and then it was a simple Vlookup.

    Thanks for the help.

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Great. Gald to help. And appreciate your follow up for everyone who reads the forums.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •