Formula for Returning values in another spreadsheet
 

I need help with a formula:

I have two separate spreadsheets: one that contains production jobs
scheduled for manufacturing (Job Orde 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 and if there
is a match, i want it to return the value on the same row in column D, E and
F 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.

=VLOOKUP(D2,'Other Sheet'!$B$2:$F$100,3,0)

for D, change the 3 to 4 for E and to 5 for F and put the formula in
3 different columns, replace Other Sheet with the sheet name where you do
the lookup and the table B2:F100 with the real size table, if not found it
will return a #N/A error. if you want to change that you can use

=IF(ISNUMBER(MATCH(D2,'Other Sheet'!$B$2:$B$100,0)),VLOOKUP(D2,'Other
Sheet'!$B$2:$F$100,3,0),"not found")

Regards,

Peo Sjoblom


"lrbest4x4xfar" wrote in message
...
I need help with a formula:

I have two separate spreadsheets: one that contains production jobs
scheduled for manufacturing (Job Orde 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 and if
there
is a match, i want it to return the value on the same row in column D, E
and
F 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.