I have two separate spreadsheets: one that contains production jobs
scheduled for manufacturing (Job Order Reports and Press Capacity
Planning.xls) and another spreadsheet that contains all raw material in
inventory (Shipping and Receiving Data). I want to write a formula on the
production jobs spreadsheet that essentially does the following: When you
enter a part number in column D (each row contains separate scheduled jobs
with the part number in column D, I want the formula to look in the shipping
spreadsheet and find where the same part number is in column B or C or D
(there are up to 3 part numbers that can use the material) and if there
is a match, i want it to return the value on the same row in column G, H and
I which is # of Matl, Weight and count respectively and place these values in
the production spreadsheet in cell V, W and X on the same row as the part
number the values are associated with.

What this formula will do for me is when the production scheduler enters job
information on a row in the production spreadsheet, the formula will
automatically provide iinventory nformation associated with the part number
entered from the inventory spreadsheet without the manager having to look in
both spreadsheets and make the correlation.

The following formula works but it doesn't look up parts in column C or D.
It just looks it up in Column B (I have this formula in Column V, W, and X
with slight changes that tell what column to return the value to):

=IF(ISNUMBER(MATCH($D6,'[Shipping and Receiving Data.xls]Raw
Material'!$B$2:$B$100,0)),VLOOKUP($D6,'[Shipping and Receiving Data.xls]Raw
Material'!$B$2:$I$100,6,0),"0")

=IF(
ISNUMBER(MATCH($D6,'[Shipping and Receiving Data.xls]Raw
Material'!$B$2:$B$100,0)),
VLOOKUP($D6,'[Shipping and Receiving Data.xls]Raw
Material'!$B$2:$I$100,6,0),IF(
ISNUMBER(MATCH($D6,'[Shipping and Receiving Data.xls]Raw
Material'!$C$2:$C$100,0)),
VLOOKUP($D6,'[Shipping and Receiving Data.xls]Raw
Material'!$C$2:$I$100,5,0),IF(
ISNUMBER(MATCH($D6,'[Shipping and Receiving Data.xls]Raw
Material'!$D$2:$D$100,0)),
VLOOKUP($D6,'[Shipping and Receiving Data.xls]Raw
Material'!$D$2:$I$100,4,0),"0")))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"lrbest4x4xfar" wrote in message
...
I have two separate spreadsheets: one that contains production jobs
scheduled for manufacturing (Job Order Reports and Press Capacity
Planning.xls) and another spreadsheet that contains all raw material in
inventory (Shipping and Receiving Data). I want to write a formula on the
production jobs spreadsheet that essentially does the following: When you
enter a part number in column D (each row contains separate scheduled jobs
with the part number in column D, I want the formula to look in the
shipping
spreadsheet and find where the same part number is in column B or C or D
(there are up to 3 part numbers that can use the material) and if there
is a match, i want it to return the value on the same row in column G, H
and
I which is # of Matl, Weight and count respectively and place these values
in
the production spreadsheet in cell V, W and X on the same row as the part
number the values are associated with.

What this formula will do for me is when the production scheduler enters
job
information on a row in the production spreadsheet, the formula will
automatically provide iinventory nformation associated with the part
number
entered from the inventory spreadsheet without the manager having to look
in
both spreadsheets and make the correlation.

The following formula works but it doesn't look up parts in column C or D.
It just looks it up in Column B (I have this formula in Column V, W, and X
with slight changes that tell what column to return the value to):

=IF(ISNUMBER(MATCH($D6,'[Shipping and Receiving Data.xls]Raw
Material'!$B$2:$B$100,0)),VLOOKUP($D6,'[Shipping and Receiving
Data.xls]Raw
Material'!$B$2:$I$100,6,0),"0")