these work, but what i would really like to do is be able to drag down so it finds the next worksheet name, and the next, etc.....
i have 26 sheets in total
I would like to offer a better, more versatile, version of the formula above:
MID(CELL("filename"),FIND("]",CELL("filename"))+1,20)
If you copy this formula in 10 different sheets, all 10 formulas will return the result of the ACTIVE sheet. Instead:
MID(CELL("filename",a1),FIND("]",CELL("filename",a1))+1,20)
This formula will return the result of the sheet where the formula exists.
Excel Magic Trick 756: Create Sequential Dates Across Sheets & Extract Sheet Name To Cell - YouTube
The length of 20 is retrictive. I think a sheet name can have 31 chars, the max length allowed. The following does not require such a specification:
=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")
By the way, the OP wants to list all sheets...
Define
Sheets by means of Insert | Name | Define or Formulas | Name Manager as referring to:
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
Invoke now in A1 on Summary...
Either:
=IF(ROWS($A$1:A1)<=COUNTA(Sheets),INDEX(Sheets,ROWS($A$1:A1)),"")
Or:
=IFERROR(INDEX(Sheets,ROWS($A$1:A1)),"")