Forum Discussion
can summary table auto update for new worksheet?
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 Thank you for your assistance. I will need to find a bit of time to work through the suggested process.
Phil_TKREO -It's doable but a big effort. I can give you some pointers.
- Start off with the 3D reference -Here is a good https://www.ablebits.com/office-addins-blog/excel-3d-reference-formula/
- We can leverage Excel's https://www.ablebits.com/office-addins-blog/excel-indirect-function/formula to make the indirect cell reference when a new sheet is added to your workbook.
- 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!
