Any way to get worksheet name from function?

[Colin]

Is there any way for me to access the name of the current Worksheet tab, to
put it into a cell? I need a cell whose name changes based on the name
entered on the Worksheet tab.

Thanks,
Colin

 

[Gord Dibben]

Copied from Bob Phillips' site........note: leave the "filename" as is. Do
not substitute your file name.

File path, file and worksheet name:
=CELL("filename",A1)

File path only:
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

File name only:
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

The sheet name:
=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

Restrictions
This technique only works for workbooks that have been saved, at least once.

http://www.xldynamic.com/source/xld.xlFAQ0002.html

Bob's site is temporarily down.


Gord Dibben MS Excel MVP

 

[Colin]

That's perfect. Thanks!

I had looked at the cell() function, but had not tried the "filename"
option.

Related, but I don't think this is possible - along the same lines, is there
any way to get the name of the next sheet?

Thanks again,
Colin

Copied from Bob Phillips' site........note: leave the "filename" as is.
Do
not substitute your file name.

File path, file and worksheet name:
=CELL("filename",A1)

File path only:
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

File name only:
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

The sheet name:
=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

Restrictions
This technique only works for workbooks that have been saved, at least
once.

http://www.xldynamic.com/source/xld.xlFAQ0002.html

Bob's site is temporarily down.


Gord Dibben MS Excel MVP

Is there any way for me to access the name of the current Worksheet tab,
to
put it into a cell? I need a cell whose name changes based on the name
entered on the Worksheet tab.

Thanks,
Colin

 

[Shane Devenshire]

Hi,

You can shorten the previous suggestion to

=MID(CELL("Filename"),FIND("]",CELL("Filename"))+1,31)

31 is chosen because the maximun number of characters for a sheet name is
31. The is no need to reference any cell when using this version of the
formula.

Another point, this formula recalculates when the spreadsheet recalculates,
which means when you move from another sheet to the sheet with the formula it
will display the incorrect sheet name until the sheet recalculates.

 

[Gord Dibben]

If you leave the cell referfence in the formula, a re-calc is not necessary
when switching sheets.


Gord

 

[Colin]

Thanks!

Any way to get the names of other worksheets? In other words, is there a
reference to something like CurrentSheet+1 or CurrentSheet-1?

Thanks,
Colin

 

[Gord Dibben]

I don't know of any formula method without using a UDF

Function AnySht(Name As String, num As Integer)
Application.Volatile
N = Application.Caller.Parent.Index
If N = 1 Then
AnySht = CVErr(xlErrRef)
Else
AnySht = Sheets(N + num).Name
End If
End Function

=anysht("name",x)

where x is positive or negative number away from current sheet


Gord

 

[Colin]

Yeah, I ended up doing it in VBA, but I was hoping for something that I
could just embed in a cell so I could leave out the code for simplifying
security issues.

Thanks,
Colin