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.
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.
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.
I did as you suggested and added a Sheet1 and Sheet2 to the beginning and end of the workbook, with the formula
and it works great, Thank you for the help, it's much appreciated.