Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to autofill data from one sheet into another
I have a manufacturing/production costing worksheet in which you enter a Part
Number, setup time, setup cost/hr, production time, production cost/hr, overhead cost/hr (which is constant) and number of parts. Given that info, it calculates the cost per part. We currently have 200 parts on which we want to do a cost study and I'm looking for a way to autofill the costing worksheet with data from another spreadsheet. This 2nd spreadsheet is set up so the Part Numbers are in the rows and the columns are headed P/N (part number),Setup Time, Setup Cost, Prod Time, Prod Cost, Overhead, Order Qty. I'm looking for a way to set up the costing worksheet so all the user has to do is enter the Part Number and the corresponding data from the 2nd spreadsheet autofills in the appropriate spaces to give the cost per part. So, if I enter Part number 00-000000 in the appropriate space of the costing worksheet, it will go to the 2nd spreadsheet, find that part number and its setup time, setup cost, production time, production cost, overhead cost and number of parts will autofill into the costing worksheet. Does that make sense? Thank you so much for any help! |
#2
|
|||
|
|||
Let's say that your "main" data list is on Sheet2,
With headers in A1 to G1. Data is in A2 to G200, with PN's in Column A. Say column headers are duplicated on Sheet1. User enters PN to lookup in A2. Enter this formula in B2: =IF(ISNA(MATCH(A2,Sheet2!A2:A200,0)),"",VLOOKUP($A 2,Sheet2!$A$2:$G$200,COLUM N(B:B),0)) And copy across to G2. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "kate" wrote in message ... I have a manufacturing/production costing worksheet in which you enter a Part Number, setup time, setup cost/hr, production time, production cost/hr, overhead cost/hr (which is constant) and number of parts. Given that info, it calculates the cost per part. We currently have 200 parts on which we want to do a cost study and I'm looking for a way to autofill the costing worksheet with data from another spreadsheet. This 2nd spreadsheet is set up so the Part Numbers are in the rows and the columns are headed P/N (part number),Setup Time, Setup Cost, Prod Time, Prod Cost, Overhead, Order Qty. I'm looking for a way to set up the costing worksheet so all the user has to do is enter the Part Number and the corresponding data from the 2nd spreadsheet autofills in the appropriate spaces to give the cost per part. So, if I enter Part number 00-000000 in the appropriate space of the costing worksheet, it will go to the 2nd spreadsheet, find that part number and its setup time, setup cost, production time, production cost, overhead cost and number of parts will autofill into the costing worksheet. Does that make sense? Thank you so much for any help! |
#3
|
|||
|
|||
Thank you so much! Exactly what I needed.
"RagDyer" wrote: Let's say that your "main" data list is on Sheet2, With headers in A1 to G1. Data is in A2 to G200, with PN's in Column A. Say column headers are duplicated on Sheet1. User enters PN to lookup in A2. Enter this formula in B2: =IF(ISNA(MATCH(A2,Sheet2!A2:A200,0)),"",VLOOKUP($A 2,Sheet2!$A$2:$G$200,COLUM N(B:B),0)) And copy across to G2. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "kate" wrote in message ... I have a manufacturing/production costing worksheet in which you enter a Part Number, setup time, setup cost/hr, production time, production cost/hr, overhead cost/hr (which is constant) and number of parts. Given that info, it calculates the cost per part. We currently have 200 parts on which we want to do a cost study and I'm looking for a way to autofill the costing worksheet with data from another spreadsheet. This 2nd spreadsheet is set up so the Part Numbers are in the rows and the columns are headed P/N (part number),Setup Time, Setup Cost, Prod Time, Prod Cost, Overhead, Order Qty. I'm looking for a way to set up the costing worksheet so all the user has to do is enter the Part Number and the corresponding data from the 2nd spreadsheet autofills in the appropriate spaces to give the cost per part. So, if I enter Part number 00-000000 in the appropriate space of the costing worksheet, it will go to the 2nd spreadsheet, find that part number and its setup time, setup cost, production time, production cost, overhead cost and number of parts will autofill into the costing worksheet. Does that make sense? Thank you so much for any help! |
#4
|
|||
|
|||
Appreciate the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "kate" wrote in message ... Thank you so much! Exactly what I needed. "RagDyer" wrote: Let's say that your "main" data list is on Sheet2, With headers in A1 to G1. Data is in A2 to G200, with PN's in Column A. Say column headers are duplicated on Sheet1. User enters PN to lookup in A2. Enter this formula in B2: =IF(ISNA(MATCH(A2,Sheet2!A2:A200,0)),"",VLOOKUP($A 2,Sheet2!$A$2:$G$200,COLUM N(B:B),0)) And copy across to G2. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "kate" wrote in message ... I have a manufacturing/production costing worksheet in which you enter a Part Number, setup time, setup cost/hr, production time, production cost/hr, overhead cost/hr (which is constant) and number of parts. Given that info, it calculates the cost per part. We currently have 200 parts on which we want to do a cost study and I'm looking for a way to autofill the costing worksheet with data from another spreadsheet. This 2nd spreadsheet is set up so the Part Numbers are in the rows and the columns are headed P/N (part number),Setup Time, Setup Cost, Prod Time, Prod Cost, Overhead, Order Qty. I'm looking for a way to set up the costing worksheet so all the user has to do is enter the Part Number and the corresponding data from the 2nd spreadsheet autofills in the appropriate spaces to give the cost per part. So, if I enter Part number 00-000000 in the appropriate space of the costing worksheet, it will go to the 2nd spreadsheet, find that part number and its setup time, setup cost, production time, production cost, overhead cost and number of parts will autofill into the costing worksheet. Does that make sense? Thank you so much for any help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort data from 1 work sheet into another | Excel Worksheet Functions | |||
Does excel recognise names rather than cells? | Excel Worksheet Functions | |||
Merger Two Data Sheet | Excel Discussion (Misc queries) | |||
Copying multiple sheets from one book 2 another and undertake spec | Excel Discussion (Misc queries) | |||
Function to automatically insert a new sheet as a result of data entry? | Excel Worksheet Functions |