Question: I have a workbook with many sheets. How do i sum values from same place on every sheet throughout the entire workbook?
Answer: You need to create a macro that creates an array of your sheet names.
I found a macro on C Pearson site.
How to use this macro on your workbook:
- Enable the developer tab on the ribbon.
- Go to developer tab.
- Click "Visual Basic"
- Right click with mouse button on VBAProject (Your file)
- Select Insert and Module
- Copy VBA from C Pearson site
How to retrieve and sum cell values on cell A1:A3 from every sheet:
- Use the VBA by typing =AllSheetNames() in a cell range (A1:A3) and press Ctrl + Shift + Enter
- In B1:B3 type "=INDIRECT(A1&"!"&"C1")
How to refresh Sheet list if a sheet name is added or renamed:
- Select all cells that contain sheet names
- Press Ctrl +Shift + Enter