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.
    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

Participate now!

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