Hello there everyone!
So ive been having trouble with figuring out how to go about this.
What i have is a table on a sheet called 'Tooling Data'. The table is called 'ToolingData_Table' and it has a few different columns, one column of which is full of part numbers, another with # of corners, # of pieces per corner and lastly a Qty/Week column.
On another sheet called 'Production Data' i have a table called 'ProductionData_Table' which has a Part # column, and a production/week column
So what i want to do is on my 'Tooling Data' sheet in the 'ToolingData_Table' i want a formula in the 'QTY/Week' column that looks up the value for part number in its row, use that value to then correspond it with the 'ProductionData_Table' to get the corresponding production number, and then divide that number by the product of (# of corners * # of pieces per corner)
Heres a table to explain what i mean:
This is a barebones of my 'ToolingData_Table' on sheet 'Tooling Data'
<tbody>
</tbody>
Here is a barebones of my 'ProductionData_Table' on Sheet 'Production Data'
<tbody>
</tbody>
So looking at these examples, i want a formula that i put into the QTY/Week column that grabs the part # from its row [xyz], then uses that part number in the 'Production Data_Table' to get the 'Production/Week' value [9000], then have that divided by the product of (# of corners [4] * # of pieces per corner [250])
i was thinking of using the Index function since i want the formula to be flexible and able to work if i add/delete columns, etc. and each of these tables will grow over time as more parts and inserts are used
any help on this would be greatly appreciated since I'm not too strong with the INDEX function yet. Thanks a ton in advance!
So ive been having trouble with figuring out how to go about this.
What i have is a table on a sheet called 'Tooling Data'. The table is called 'ToolingData_Table' and it has a few different columns, one column of which is full of part numbers, another with # of corners, # of pieces per corner and lastly a Qty/Week column.
On another sheet called 'Production Data' i have a table called 'ProductionData_Table' which has a Part # column, and a production/week column
So what i want to do is on my 'Tooling Data' sheet in the 'ToolingData_Table' i want a formula in the 'QTY/Week' column that looks up the value for part number in its row, use that value to then correspond it with the 'ProductionData_Table' to get the corresponding production number, and then divide that number by the product of (# of corners * # of pieces per corner)
Heres a table to explain what i mean:
This is a barebones of my 'ToolingData_Table' on sheet 'Tooling Data'
Dept. | Part # | Op # | Type of Item | Item Code | Stock # | # of Corners | # of pieces per corner | QTY/Week |
auto | xyz | 10 | Insert | qwerty | 1234 | 4 | 250 | =9000/(4*250) |
<tbody>
</tbody>
Here is a barebones of my 'ProductionData_Table' on Sheet 'Production Data'
Part # | Production/Week |
xyz | 9000 |
abc | 10000 |
<tbody>
</tbody>
So looking at these examples, i want a formula that i put into the QTY/Week column that grabs the part # from its row [xyz], then uses that part number in the 'Production Data_Table' to get the 'Production/Week' value [9000], then have that divided by the product of (# of corners [4] * # of pieces per corner [250])
i was thinking of using the Index function since i want the formula to be flexible and able to work if i add/delete columns, etc. and each of these tables will grow over time as more parts and inserts are used
any help on this would be greatly appreciated since I'm not too strong with the INDEX function yet. Thanks a ton in advance!