Hi All,
I have a spreadsheet with different bills of materials (BOM) on it for various products, with a master stock sheet at the start. I am trying to get the master stock sheet to track the stock required over all of the BOM sheets when quantities are added on them. Some of the materials are repeated on the BOM sheets. Their could be upto 60 different sheets when the spreadsheet is finished. This is the formula I have so far.
=IF(ISNA(VLOOKUP(A26,'900-1004-000'!$L$3:$M$61,2,0)),0,(VLOOKUP(A26,'900-1004-000'!$L$3:$M$61,2,0)))+IF(ISNA(VLOOKUP(A26,'900-1004-010'!$L$3:$M$61,2,0)),0,(VLOOKUP(A26,'900-1004-010'!$L$3:$M$61,2,0)))+IF(ISNA(VLOOKUP(A26,'900-1004-030'!$L$3:$M$61,2,0)),0,(VLOOKUP(A26,'900-1004-030'!$L$3:$M$61,2,0))).
It works but for upto 60 sheets it would be huge infact wouldn't work then as I would run out of characters.
Master stock sheet example:
A J
2715 13
2716 25
2717 25
2718 25
2719 25
2720 25
2721 25
2722 25
2723 35
Where column J has the formula in it for the total quantity required and column A being the Material number.
On the BOM sheets, column L is the material number and column M is the quantity.
Can anyone recommend a simpler formula?
I am using Microsoft office 2010