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

New 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.

https://jkp-ads.com/downloadnl.asp

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):
=Sheet2!TabName

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