make a cell equal a worksheet name

C

cyndi

Hi, Lets say I have a worksheet named budget, and I want the name of that
worksheet to appear in a cell, is there a formula that can do that, for
example =budget!, that doesnt seem to work for me, Thanks
 
K

Kevin B

The following custom function will do it for you.

Function WhoAmI() As String

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet

Application.Volatile
WhoAmI = ws.Name

Set wb = Nothing
Set ws = Nothing

End Function

In Excel, press Alt + F11 to open the VBE. On the VBE menu, click INSERT
and select MODULE. Copy or type the formula above from the line Function
WhoAmI() through End Function inclusive and paste it in the module.

You can then use the function by type =WhoAmI() in the cell of your choice.
 
E

Elkar

See if this works for you:

=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1),1)+1,1024)

HTH,
Elkar
 
C

cyndi

your the best, that formula so worked, so did the macro, but I really was
looking for a formula, thanks so much

Elkar said:
See if this works for you:

=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1),1)+1,1024)

HTH,
Elkar


cyndi said:
Hi, Lets say I have a worksheet named budget, and I want the name of that
worksheet to appear in a cell, is there a formula that can do that, for
example =budget!, that doesnt seem to work for me, Thanks
Click to expand...
Click to expand...
 
Top