Lookup Problem

[wayneryeven]

Hi All
Wondering can anyone help.

I have a worksheet (Sheet1) that holds this data:

Item Date Stock
A 10/08 50
A 12/08 30

Sheet 2 holds Supply information:

Item Date Supply
A 11/08 50

What i need to do is lookup in Sheet1 to see if there is due any supply of an item and add this to a new column D.

However, in the above example exposes a problem- The stock data does not exist for every date. In this case what i would want is for it to take the next date it finds greater than supply date.

Therefore, above, the supply date is 11/08. In column D of Sheet1 i would like to see Item A on the 12/08 to change to 50.

Firstly, can this be achieved? And secondly, any pointers?

Many thanks for all feedback, I hope i have explained it but feel free to ask for further detail if required?

Thanks

 

[SkipVought]

hi,

Rather than a lookup, I would use MS Query via Data > Get External Data... faq68-5829

Join the two tables like this...

Select Item, Date, Stock

From [Sheet1$]

UNION ALL

Select Item, Date, Stock

From [Sheet2$]

Sort by Item, Date when your data is returned.

Result:
[tt]
Item Date Stock
A 10/08 50
A 12/08 30
A 11/08 50
[/tt]
I HOPE, for your sake, that your Date column contains REAL DATES!



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Oops, I missed something

Select Item, Date, Stock, 0 as Supply

From [Sheet1$]

UNION ALL

Select Item, Date, , Supply

From [Sheet2$]

Result:
[tt]
Item Date Stock Supply
A 10/08 50
A 12/08 30
A 11/08 50
[/tt]



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wayneryeven]

Hey Skip
SOunds just the ticket! Now to find out how to do this in Excel 2007..

Data, External Source, Excel> Select the .xlsx im working in and i get "This data sournce contains no visible tables". Ill peservere.

Many thanks for your anser, ill report back findings and close accordingly!

 

[SkipVought]

In the Add Tables Window, click the OPTIONS Button and CHECK ALL boxes.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]