Multilevel BOM question

[pstrucks]

I'm trying to create a formula which will help me calculate quantities in a BOM based on the QTY need by the parent item. In the example above this would mean that if UMY-1000 (line 11) needs 10 units, I would need 10 units of ASY-1032 as per column F ASY-1032 is need in a QTY of 1 per parent item. Column A shows the levels in the BOM. I tried using the following formula in column I =F2*IF(A2=1, 1, LOOKUP(2, 1/(A$1:A2=A2-1), I$1:I1)) but this is not working.

Anyone have any better idea? the BOM for this product is >900 lines so a formula would help over having to spend hours doing this line-by-line.

Thanks!

 

[bebo021999]

1) You, yourself, deeply understand your issue. We, helpers, absolutely not.
What is BOM? ("Board of Management"?)
2) why does UMY-1000 need 10 units?
3) Instead of showing error sign from un-worked formula, try to input the desired results in column I