If you have a lot of sheets and
are positively allergic to VBA, you can
automate Chuck's excellent method thus:
Right-click on any tab Select all sheets
(Color all tabs to make the new selection obvious)
Enter Chuck's modified formula into A2:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,42)
Now all worksheets will show their tab name at A2.
With all tabs still selected, enter a 1 into A3.
Into A4 enter this formula:
=SUM('*'!A3)
Note that Excel expands this formula. Sample:
=SUM(SheetA:SheetM!A3,SheetO:SheetZ!A3)
Find !A3* Replace !A3) Replace All
Except for the Summary sheet, the first sheet,
the sheets will now have sequential numbers at A4.
(Page of Pages application?)
Change A4 on the Summary sheet to 0.
Select all sheets and enter at A1
="Page"&A4
Select A1:A2 and
Insert Name Create Top Row
On the Summary sheet at B1 enter and copy down:
=INDIRECT("Page"&ROW())