Tom’s Tutorials For Excel: Using a Formula to Get Your Active Worksheet’s Name, and Active Workbook’s Path and Name

Tom’s Tutorials For Excel: Using a Formula to Get Your Active Worksheet’s Name, and Active Workbook’s Path and Name

Here are two formulas, one to return the active worksheet’s name, and the other to return the active workbook’s full path and name. In each case, please be sure to save the workbook at least once.

The formula that returns the active worksheet’s name is
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)

The formula that returns the active worksheet’s full path and name is
=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[","")

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
5 comments on “Tom’s Tutorials For Excel: Using a Formula to Get Your Active Worksheet’s Name, and Active Workbook’s Path and Name
  1. Aryanpandith says:

    Tom this is very useful formula to find worksheet name and path. Thanks.

  2. Funtom says:

    These are useful indeed, but actually the upper formula gets the parent sheet of the cell, not the actual active sheet in Excel. Place =Sheet3!$F$5 to Sheet2 or Sheet1 and you still get “Sheet 3” even though the Sheet3 is inactive.

    I would be happy to find formula to get the actual active sheet, so that I can refer to it from inactive sheets. Is there a solution to this?

    • Tom Urtis says:

      If it were me, I’d not rely on a formula to tell you what the active sheet is, but use VBA instead. In your workbook module, using a worksheet named “Budget” as the example sheet that, in its cell A1, will show whatever the active worksheet name is:

      Private Sub Workbook_SheetActivate(ByVal Sh As Object)
      Worksheets(“Budget”).Range(“A1”).Value = ActiveSheet.Name
      End Sub

      Then, in any cell on any or all worksheet(s) you can have this formula:
      =Budget!A1

  3. Annie says:

    Can you help me with a formula to take the active sheet cell g29 + the next sheet to the right in the workbook cell j29?

    • Tom Urtis says:

      Will the next worksheet to the right always (always means always) be the same name? If so, a native worksheet formula would be the way to go. If not, we’d need a UDF to get the active sheet index + 1 to identify the next sheet to the right. Either way, your request is possible.

Leave a Reply

Your email address will not be published.

*