Hi all,
I was hoping for some help with a problem i have.
I am doing a report to find the amount of parts used during our production process. I have one tab with all the products, parts, amount of parts per product and total amount of products built.
Some of the parts are used in different products so i am trying to find a way to do a lookup of part number across the products, multiple it by the amount used per product and then multiple it by how many products we have built.
Below is a rough example of how it looks, there are other columns and on my database but if i can find the correct type of formula i can adjust it to my needs
<tbody>
</tbody>
I then have another tab on my report with a list of part number
<tbody>
</tbody>
I have been playing with a few formulas but i can get one to work.
The best one i have found is using a CSE array formula {=SUM(table1!$D$2:$D$10*IF(A2=TRANSPOSE(table1!$B$2:$B$10),TRANSPOSE(table1!$C$2:$C$10),0))}
This is giving me crazy values and i have been searching around for other ways to get the results i want but i cant figure it out.
Any help would be greatly appreciated.
Regards
Brothwood
I was hoping for some help with a problem i have.
I am doing a report to find the amount of parts used during our production process. I have one tab with all the products, parts, amount of parts per product and total amount of products built.
Some of the parts are used in different products so i am trying to find a way to do a lookup of part number across the products, multiple it by the amount used per product and then multiple it by how many products we have built.
Below is a rough example of how it looks, there are other columns and on my database but if i can find the correct type of formula i can adjust it to my needs
Product | part no | part qty | built qty |
product1 | part1 | 6 | 4 |
product2 | part3 | 2 | 0 |
product3 | part2 | 4 | 5 |
product4 | part3 | 4 | 3 |
product5 | part1 | 2 | 2 |
product6 | part1 | 3 | 1 |
product7 | part2 | 4 | 3 |
product8 | part3 | 3 | 7 |
product9 | part1 | 1 | 0 |
<tbody>
</tbody>
I then have another tab on my report with a list of part number
part no | total qty used |
part1 | |
part2 | |
part3 |
<tbody>
</tbody>
I have been playing with a few formulas but i can get one to work.
The best one i have found is using a CSE array formula {=SUM(table1!$D$2:$D$10*IF(A2=TRANSPOSE(table1!$B$2:$B$10),TRANSPOSE(table1!$C$2:$C$10),0))}
This is giving me crazy values and i have been searching around for other ways to get the results i want but i cant figure it out.
Any help would be greatly appreciated.
Regards
Brothwood