tab name = cell value

  • Thread starter Thread starter jatman
  • Start date Start date
J

jatman

is it possible to have a cell value equal the same as a sheet (tab) name?
just something simple as =sheet1 or something

thank you,
 
I don't know how, & I can't remember who to credit with the solution but
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) is the formula
to use.
I believe the workbook has to be saved at least once for this to work.

Cheers

Paul
 
jatman said:
is it possible to have a cell value equal the same as a sheet (tab) name?
just something simple as =sheet1 or something
Click to expand...

Another technique, credits to Harlan,
which enables usage for all sheets at one go
(same proviso - book must be saved beforehand)

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly.

Then test/use in any sheet, in any cell, eg: =WSN
will return the particular sheetname

---
 
You can do other, similar, things too:
http://www.mcgimpsey.com/excel/formulae/cell_function.html


Regards,
Ryan--

--
RyGuy


Max said:
jatman said:
is it possible to have a cell value equal the same as a sheet (tab) name?
just something simple as =sheet1 or something
Click to expand...

Another technique, credits to Harlan,
which enables usage for all sheets at one go
(same proviso - book must be saved beforehand)

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly.

Then test/use in any sheet, in any cell, eg: =WSN
will return the particular sheetname

---
Click to expand...
 
Is it possible to include this as a function in "Personal" so that it is
available to all workbooks, perhaps with some error checking for unsaved
workbooks?

Cheers

Paul

Max said:
jatman said:
is it possible to have a cell value equal the same as a sheet (tab) name?
just something simple as =sheet1 or something
Click to expand...

Another technique, credits to Harlan,
which enables usage for all sheets at one go
(same proviso - book must be saved beforehand)

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly.

Then test/use in any sheet, in any cell, eg: =WSN
will return the particular sheetname

---
Click to expand...
 
Function SheetName(Optional ByVal rng As Range) As String
Application.Volatile
If rng Is Nothing Then Set rng = Application.Caller
SheetName = rng.Parent.Name
End Function

=SheetName()

Has no arguments and doesn't care if the workbook is saved.


Gord Dibben MS Excel MVP

Is it possible to include this as a function in "Personal" so that it is
available to all workbooks, perhaps with some error checking for unsaved
workbooks?

Cheers

Paul

Max said:
jatman said:
is it possible to have a cell value equal the same as a sheet (tab) name?
just something simple as =sheet1 or something
Click to expand...

Another technique, credits to Harlan,
which enables usage for all sheets at one go
(same proviso - book must be saved beforehand)

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly.

Then test/use in any sheet, in any cell, eg: =WSN
will return the particular sheetname

---
Click to expand...
Click to expand...
 
Thanks Gordon,

this really works fine... even in a Spanish Excel

Gracias,

Fernando

Gord Dibben said:
Function SheetName(Optional ByVal rng As Range) As String
Application.Volatile
If rng Is Nothing Then Set rng = Application.Caller
SheetName = rng.Parent.Name
End Function

=SheetName()

Has no arguments and doesn't care if the workbook is saved.


Gord Dibben MS Excel MVP

Is it possible to include this as a function in "Personal" so that it is
available to all workbooks, perhaps with some error checking for unsaved
workbooks?

Cheers

Paul

Max said:
:
is it possible to have a cell value equal the same as a sheet (tab) name?
just something simple as =sheet1 or something

Another technique, credits to Harlan,
which enables usage for all sheets at one go
(same proviso - book must be saved beforehand)

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly.

Then test/use in any sheet, in any cell, eg: =WSN
will return the particular sheetname

---
Click to expand...
Click to expand...
Click to expand...
 
Back
Top