Forum Discussion
Worksheet names
Hi IS there a way of copying or exporting a list of the worksheet names so they can be copied and pasted into an overview worksheet at the start of the workbook
See for example List sheet names with formula
- that technique works in desktop version but will require the workbook to be saved in a macro enabled version (.xlsm). If you just need it 1 time and don't want to save it as a macro sheet then you can ignore the warning and get the list of sheets.
If you want this to be dynamic (i.e. have a list that updates as people add/remove sheets especially if they are copying a template sheet) then you can add a formula on each sheet that will regurgitate its own name (note the must be saved locally):
=LET(path,CELL("filename",A1),RIGHT(path,LEN(path)-SEARCH("]",path)))
or if you don't have LET()
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1)))
and then use the following formula to create the list of sheets (you will need to input the first and last sheet in the range or you can use indirect to build that range name using 2 cells on the sheet where you enter those 2 sheet names:
=TRIM(MID(TEXTJOIN(REPT(" ",100),,Sheet1:Sheet5!A1),SEQUENCE(SHEETS(),,1,100),100))
and if you don't have dynamic arrays (and assuming you start on row 1 and drag down):
=INDEX(TRIM(MID(TEXTJOIN(REPT(" ",100),,Sheet1:Sheet5!$A$1),SEQUENCE(SHEETS(),,1,100),100)),ROW())
