How to dynamically fetch #Microsoft #Excel Worksheet Tab Names using a Formula

One of my clients recently reached out to me to help build a series of reports in Microsoft Excel for the purpose of analyzing their spending across multiple Microsoft Azure subscriptions.

My client wants one Microsoft Excel workbook which contains a series of reports arranged in multiple Excel worksheets.

To keep all these reports organized, each Excel worksheet tab will be uniquely named.

The Challenge

Inside each report (i.e. Excel worksheet), they would like to dynamically fetch the name of the corresponding worksheet tab using a formula. Nothing should be hard coded so that they can rename those Excel worksheet tabs in the future if required.

In this article, I will share my solution on how to dynamically fetch Microsoft Excel worksheet tab names using a re-usable formula.

The Requirement

My client wants an Excel formula to be implemented in such a way that it can be easily copied and pasted in all subsequent worksheets without any modification to the formula.

The Solution

In Excel, we will use the CELL function with the “filename” option to fetch the name of the current worksheet.

Here’s an example formula that you can use:

=CELL("filename",A1)

This formula returns the full path and name of the current worksheet tab.

Here is an example of the resulting string that is returned by this formula:

C:\AzureMentor\Azure_Subscription_Report\[DynamicallyFetchWorksheetName.xlsx]Subscription001

Next, we need to parse this resulting string and extract the worksheet tab name.

To extract only the name of the worksheet tab, we can use the MID function and the FIND function to find the position of the last occurrence of the backslash character and extract the name after that position.

Here is the formula that I use to achieve this:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

Save the Excel Workbook

Be sure to save your Excel workbook file! The CELL function with the “filename” option works best if your workbook has been saved in a standard Excel file format.

Highly re-usable

The formula above is highly re-usable. It will dynamically return the name of the current worksheet tab.

We can replace the “A1” argument in the CELL function with any cell reference that you want to use as a reference to the current worksheet.

This means, we can re-use this formula by simply copying and pasting this formula to any subsequent workbooks, worksheets, and cells.

This formula will work like a charm!

Summary

In this tip, I’ve shared how I solved a client’s request on how to dynamically fetch the name of an Excel worksheet tab that can be re-used without changes across multiple workbooks, worksheets, and cells.

Hope you find this tip helpful!