Any way to get worksheet name from function?

  • Thread starter Thread starter Colin
  • Start date Start date
C

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


Gord Dibben said:
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
Click to expand...
Click to expand...
 
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.
 
If you leave the cell referfence in the formula, a re-calc is not necessary
when switching sheets.


Gord
 
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
 
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
 
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
 
Back
Top