Thank you Ashish, It works great, just as I wanted. I have been pulling my hair out for ages trying to work it out myself - to no avail cos I'm already bald as it is!

When you copy the formula to your workbook, you must first save the workbook, close it and then reopen it.
This has to be done the first time only. Thereafter, when you copy the formula across worksheets, it will pick up the tab name.

Thank you Ashish, It works great, just as I wanted. I have been pulling my hair out for ages trying to work it out myself - to no avail cos I'm already bald as it is!

Thank you once again.

You are most welcome.

Perfection! Have searched all the google'd results, and yours was the ONLY one that worked! THANK you!

You are welcome.

Thanks! Have also been looking for some time without any results, but this formula works perfekt!

You are welcome.

That's worked a treat, many thanks

You are welcome.

It may be our institution's firewall, but I cannot see your formula in the spreadsheet. Can you post the formula online?

Hi,

This is the formula in cell B3 of sheet1 of that file

=MID(CELL("filename",O1),FIND("]",CELL("filename",O1))+1,256)

When you copy the formula to your workbook, you must first save the workbook, close it and then reopen it.

This has to be done the first time only. Thereafter, when you copy the formula across worksheets, it will pick up the tab name.

Hope this helps.

Hi,

So, as an extension of this, is it possible to have the first tab have cells that show the remaining tab names on that first tab?

Hi,

Please see this Blog post on my website.

Hope this helps.

It does not seem to work for me using Excel 2016. And why is the tabname to be found in Cell O1 ???

Hi,

It should. After writing the formula, please close and reopen the file. You may refer to any cell (not specifically cell O1).

What is the formula when wanting to display a DIFFERENT tab name, not the current/active tab?

Hi,

See if my article here helps - http://www.ashishmathur.com/generate-a-list-of-all-tabs-names-without-using-vba/.