# Vlookup with addition across multiple sheets

• 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

• Re: Vlookup with addition across multiple sheets

Excel 2010 PivotTable
Consolidate and sum BOMs on multiple sheets.
With macro.
Stepping thru the macro will illustrate a method without formulas or macro.
http://c3017412.r12.cf0.rackcdn.com/04_03_11a.xlsm
If you get *.zip, don't unzip, just rename *.xlsm

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!