Worksheet Name in Cell

  • Thread starter Thread starter Shawn Shuler
  • Start date Start date
S

Shawn Shuler

I may be overlooking something simple but how can I echo the worksheet name
in a particular cell. FOr example, I want cell A1 to always contain the
name of the worheet that cell is a part of. Cell A1 of the worksheet
"SHEET1" would contain "SHEET1". I would need it to update if the sheet
name were changed as well.
 
Hi Shawn

not all that simple :)
use this formula to put the sheet name in a cell
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

BTW you need to save the workbook first.
 
The file path and name
CELL("filename",A1)

The file path
LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

The file name
MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("fil
ename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

The sheet name
RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",
A1),1))

--
Regards
-
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
 
Since a sheet name can only have 31 characters you can shorten that to

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

Regards,

Peo Sjoblom

JulieD said:
Hi Shawn

not all that simple :)
use this formula to put the sheet name in a cell
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

BTW you need to save the workbook first.
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
Shawn Shuler said:
I may be overlooking something simple but how can I echo the worksheet name
in a particular cell. FOr example, I want cell A1 to always contain the
name of the worheet that cell is a part of. Cell A1 of the worksheet
"SHEET1" would contain "SHEET1". I would need it to update if the sheet
name were changed as well.
Click to expand...
Click to expand...
 
Hi Peo

didn't know this, thanks

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
Peo Sjoblom said:
Since a sheet name can only have 31 characters you can shorten that to

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

Regards,

Peo Sjoblom

JulieD said:
Hi Shawn

not all that simple :)
use this formula to put the sheet name in a cell
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

BTW you need to save the workbook first.
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
Shawn Shuler said:
I may be overlooking something simple but how can I echo the worksheet
name
in a particular cell. FOr example, I want cell A1 to always contain
the
name of the worheet that cell is a part of. Cell A1 of the worksheet
"SHEET1" would contain "SHEET1". I would need it to update if the
sheet
name were changed as well.
Click to expand...
Click to expand...
Click to expand...
 
What's wrong about this particular solution? Works nicely for me.

Private Sub Workbook_Open()
ThisWorkbook.Worksheets(1).Range("A1") = ThisWorkbook.Worksheets(1).Name

End Sub


Gord.
 
The said:
Cell A1 of the worksheet "SHEET1" would contain "SHEET1". I would
need it to update if the sheet name were changed as well.
Click to expand...

Your solution won't update until the workbook is closed and reopened. It
also depends on macros being enabled.

In practice your solution may be adequate for the OP, but it doesn't
meet the specification.
 
I find that this one:

RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",
A1),1))

is the only one that updates automatically as the sheet name is changed.
EXACTLY what I was looking for.


Andy Wiggins said:
The file path and name
CELL("filename",A1)

The file path
LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

The file name
MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("fil
ename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

The sheet name
RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",
A1),1))

--
Regards
-
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy


Shawn Shuler said:
I may be overlooking something simple but how can I echo the worksheet name
in a particular cell. FOr example, I want cell A1 to always contain the
name of the worheet that cell is a part of. Cell A1 of the worksheet
"SHEET1" would contain "SHEET1". I would need it to update if the sheet
name were changed as well.
Click to expand...
Click to expand...
 
Back
Top