Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Posts
    104

    Unanswered: Sum Across multiple worksheets

    I have been combing through the forums and haven't found a definitive answer to this. How can i get the sum of the same cell from multiple worksheets without knowing the exact name of the worksheet. I have an excel spreadsheet that i have purchase orders in and every purchase order has its own worksheet and the name of the worksheet is the purchase order number so the sheets have random numbers that i don't want to have to input separately. The total for that PO is in the same cell on every worksheet. I would like to sum this cell to keep track of how much i spend with each supplier. Seems like a simple problem but i'm not as skilled with Excel as i am with Access. Any help would be greatly appreciated. Essentially i want the sum of all the numbers from cell J48 from all worksheets within the same workbook, so if i add more purchase order it will automatically propagate.

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    One way to do this is to set up two "buffer" sheets. One buffer is placed at the beginning of the workbook (except for your summary sheet), the other at end of the workbook and both are left empty. New sheets added to the workbook should be placed inbetween the two buffer sheets. Your SUM formula references the dummy sheets, thus totalling everything between them.

  3. #3
    Join Date
    May 2009
    Posts
    104
    Right now i can't even get a sum of all the worksheets much less make it dynamic. Right now the only way to get a sum is to
    =SUM('52777'!J48,'52777'!J48,'52781'!J48,'52788'!J 48,'52790'!J48) and so on until i select all of the worksheets in the workbook, which is very time consuming.

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Ah, your formula's wrong. It'd be like this:

    =SUM('52777:52790'!J48)

    This will sum all the J48 cells on the sheets between '52777' and '52790' inclusive. So in the scenario I described, the two sheets referenced in this formula would be the empty "buffer" sheets.

  5. #5
    Join Date
    May 2009
    Posts
    104
    I did as you suggested and added a Sheet1 and Sheet2 to the beginning and end of the workbook, with the formula
    =SUM('sheet1:sheet2'!J48)
    and it works great, Thank you for the help, it's much appreciated.
    Last edited by dbshaft; 08-11-11 at 23:22.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •