Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
=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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to make Negative Values Positive & Positive Values Negative? | Excel Discussion (Misc queries) | |||
If formula that looks at multiple cells and values, and then calul | Excel Discussion (Misc queries) | |||
FORMULA REQD FOR ADD ING DATES AND VALUES AND PART NUMBERS | Excel Discussion (Misc queries) | |||
Need formula to check values of data in several cells as criteria | Excel Worksheet Functions | |||
manipulating formula values | Excel Worksheet Functions |