V_Lookup question!
 

I would like to do a v_lookup for stock between different worksheets. The
order sheet that I get in looks like this with 5 columns

Ship To Location Product PT No Invoice No Qty
AUS V795 MM064 PMC1215 100
COV V795 MM064 PMZ1228 3
LON V795 MM064 PMC1213 100
LEEDS V795 MM064 PMZ1211 550
NOTT V795 MM064 PMC1214 750

I then have an individual sheet for each Ship to Location and I want to
write a v_lookup in each one of these sheets against each product that will
tell me if the Product has been requested for the location on the order sheet
and if it has then I want the Invoice number and Qty to come over into each
specific warehouse sheet. The warehouse sheets look like this:

Ship To Location PT No Invoice No Qty
COV MM064
COV MM065
COV MM066
COV MM067

Any help on this would be hugely appreciated!

--
....CuriousMelly...

V_Lookup question!
 

Try this:

On the COV sheet under Invoice No enter this array formula:

=INDEX(Order!D$2:D$6,MATCH(1,(Order!$A$2:$A$6="COV ")*(Order!$C$2:$C$6=$B2),0))

Copy across to the Qty column then down as needed.

On each of the other sheets replace "COV" with the sheet name or use a cell
reference.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"CuriousMelly" wrote in message
...
I would like to do a v_lookup for stock between different worksheets. The
order sheet that I get in looks like this with 5 columns

Ship To Location Product PT No Invoice No Qty
AUS V795 MM064 PMC1215 100
COV V795 MM064 PMZ1228 3
LON V795 MM064 PMC1213 100
LEEDS V795 MM064 PMZ1211 550
NOTT V795 MM064 PMC1214 750

I then have an individual sheet for each Ship to Location and I want to
write a v_lookup in each one of these sheets against each product that
will
tell me if the Product has been requested for the location on the order
sheet
and if it has then I want the Invoice number and Qty to come over into
each
specific warehouse sheet. The warehouse sheets look like this:

Ship To Location PT No Invoice No Qty
COV MM064
COV MM065
COV MM066
COV MM067

Any help on this would be hugely appreciated!

--
...CuriousMelly...