hlookup, same dates in row I'm looking up from

[Christine Flott]

Hello all,

First, thanks to all for making this such a worthwhile resource. I
feel humbled whenever I have to post a question and I get such
thought-out responses.

Here's my latest scenario. I have 23 sheets. In one sheet, I enter
purchase orders. Each Item that is being ordered has its own sheet
where certain information (order date, number, arrival and quantity)
from those purchase orders is automatically updated and further
tracked.

Purchase Order Entry Sheet
PO Date 9/12/03 11/20/03 11/20/03 12/14/03
PO Number 123 234 345 456
PO Est. Arrival 9/30/03 11/30/03 12/5/03 12/30/03
Item 1 45 100
Item 2 222
Item 3 11 18
Item 4 24

Each Item has a sheet. I have HLookups looking at the PO Date on the
PO Entry Sheet and filling in how many items were ordered. The problem
comes when I have two orders with the same date (see 11/20). Only one
of those orders is picked up by the HLookup. I need it to find both so
that all orders are accounted for on the Item sheets. In my example
above, I would have four Item sheets, but only (in my situation) is
the 2nd order on 11/20 recorded in the item sheets. Item_1 and Item_2
orders for 11/20 are not being picked up.

This is very difficult to explain. I hope I've made sense. I'm using
Excel 2002.

Please let me know if you need more information. Thank you one and
all!

Christine Flott

 

[ryanb.]

This is not set up the best because you only have one cell for a PO number
yet you could possibly have more than one PO number per item per date. Is
displaying the PO number a necessity? Could it be replaced with a count of
PO's for that date, with the item quantities on all PO's on that date
summed? (ie under 11/20/03 instead of 234 and 345 in separate columns, it
would list 2 for 2 PO's, and item 3 would equal 29).

ryanb.

 

[Christine Flott]

I made a similar solution for another "challenge" I had with lookups.
Maybe I'll have to do it again for this. Thanks for your input and a
different way of looking at the problem.

Christine