Pulling the name of a Sheet Tab into a Cell, Create summary list of sheet tabs in workbook

Occasional Contributor

Hopefully these are a simple questions. I've searched for this but just haven't found what I needed.


Goal 1: Have a Cell that pulls the name of the sheet tab it's in. To be clear, not pulling data from cell in the sheet or a different sheet, I just want the name of that Sheet Tab.


Goal 2: I want to be able to Populate Cells in a Summary sheet of all the Sheet Tab names in that Workbook.


Thanks for your help.



1 Reply
I believe Jan Karel Pieterse has a "sheet tools" add-in that will create a table of contents for you.


Otherwise, you could use the CELL function to return the filename and parse it to get the worksheet name. Personally, I usually add a local named formula, called TabName, to each worksheet using this:

=MID(CELL("filename",INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1, 255)

Then, link to it (say the above name was added to Sheet2):

Note that the name is local, so the scope will be the worksheet. And, you would need to add one for each worksheet.