can summary table auto update for new worksheet?

Copper Contributor

Is it possible to add identical worksheets to a workbook, and then have a summary table in the same workbook that automatically adds data from the worksheets added?

2 Replies

@Phil_TKREO -It's doable but a big effort.  I can give you some pointers.


  1. Start off with the 3D reference -Here is a good reference 
  2. We can leverage Excel's Indirect formula to make the indirect cell reference when a new sheet is added to your workbook.
  3. Complement these formulas with latest VSTACLK and HSTACK along with SHEETS-SHEET formula to achieve what you are looking for.

Limitations:  a). We need to manually refresh the workbook once a new sheet is created as SHEETS() formula doesn't auto calculate. b). We need to be cautious when we delete a sheet as it can break the 3d reference range


I hope this helps!

@Phil_TKREO Thank you for your assistance.  I will need to find a bit of time to work through the suggested process.