make a cell equal a worksheet name

[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

[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.
--
Kevin Backmann


"cyndi" wrote:

> 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
>

[Elkar]

See if this works for you:

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

HTH,
Elkar


"cyndi" wrote:

> 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
>

[cyndi]

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

"Elkar" wrote:

> See if this works for you:
>
> =MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1),1)+1,1024)
>
> HTH,
> Elkar
>
>
> "cyndi" wrote:
>
> > 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
> >

[JimmyJames88]

Thank you for this solution. Used it on excel 2007 and worked perfect!

J.