SHEET TAB NAME - show in cell A1??

[itr674]

How can I get the sheet tab name to show in cell A1 without having to RUN a macro?
This message was edited by em on 2003-02-09 17:31

 

[itr674]

I was able to get this to work...

=MID(MID(CELL("filename"),SEARCH("]",CELL("filename"))+1,LEN(CELL("filename"))-SEARCH("]",CELL("filename"))),5,30)

 

[Aladin Akyurek]

I was able to get this to work...

=MID(MID(CELL("filename"),SEARCH("]",CELL("filename"))+1,LEN(CELL("filename"))-SEARCH("]",CELL("filename"))),5,30)

Use at least

CELL("filename",A1)

instead of just

CELL("filename")

 

[itr674]

Aladin--why use A1 in the formula??

 

[Aladin Akyurek]

Aladin--why use A1 in the formula??

In order to anchor the result to the worksheet in which the formula is used. Otherwise, you might get strange results.

 

[itr674]

roger that...

I assume using A1 is saying to Excel "put the result in A1".

 

[Aladin Akyurek]

roger that...

I assume using A1 is saying to Excel "put the result in A1".

=CELL("filename",A1)

entered in A1 is quite legal, an exception so to speak.

 

[Barry Katcher]

WAY COOL, ALADIN. It's a Keeper! Here's what they look like, plus two more Morefunc formulas.

Keepers3.xls
	A	B	C	D
1		Path/FileName/SheetNameinCell
2		ThankstoAladin,MrExcelmessageboard
3		http://www.mrexcel.com/board2/viewtopic.php?t=13683
4	Usingregularformulas
5	Path,FileandSheetNameinCell:	C:\Excelfiles\Keepers\[Keepers3.xls]MySheetName
6		=CELL("filename",B6)
7	SheetNameOnlyinCell:	MySheetName
8		=MID(B6,SEARCH("]",B6)+1,LEN(B6)-SEARCH("]",B6))
9		-or-
10		MySheetName
11		=MID(CELL("filename",B6),SEARCH("]",CELL("filename",B6))+1,LEN(CELL("filename",B6))-SEARCH("]",CELL("filename",B6)))
12
13	UsingMoreFuncAdd-infunction
14	Path,FileandSheetNameinCell:	C:\Excelfiles\Keepers\Keepers3.xls|"MySheetName"
15		=FILENAME(TRUE)&"|"""&SHEETNAME()&""""
16	PathandFileNameinCell:	C:\Excelfiles\Keepers\Keepers3.xls
17		=FILENAME(TRUE)
18	SheetNameOnlyinCell:	MySheetName
19		=SHEETNAME()
My Sheet Name

 

[goblin]

No matter how much I try, I cannot make

=CELL("filename")

do something strange. Can anyone help? And as sheetnames can not be longer than 31 characters, the formula for the sheetname can be shortened to:

=MID(CELL("filename"),SEARCH("]",CELL("filename"))+1,32)

which is SO much simpler to understand!!

 

[Aladin Akyurek]

No matter how much I try, I cannot make

=CELL("filename")

do something strange. Can anyone help? ...

Goblin,

In A1 in Sheet1 enter:

=CELL("filename")

In say E1 in Sheet2 enter:

=Sheet1!A1

 

[goblin]

Hey, this is way cool!!!

It can even be useful! Maybe. It shows you on what sheet you were when the last calculation took place, Cell being a volatile function an all. What you're going to do with that information is still being speculated but I'm sure I'll think of something, sometime