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),"[","")
Tom this is very useful formula to find worksheet name and path. Thanks.
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?
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
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?
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.