[Rex Deckard]

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.

[shades]

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.

[Rex Deckard]

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.

[Rex Deckard]

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.

[shades]

Great. Gald to help. And appreciate your follow up for everyone who reads the forums.