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]

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

goblin - I am having to use "prefixes" on sheet names so my table of contents maker knows where to put the sheet names on the TOC, and then strip the prefix off the sheet name on the TOC, AND then I also want to strip the prefix off the sheet name when it goes in A1 on that particular sheet. Hope that's not to confusing...

If you can shorten please do so.

A sample sheet name is "log3-Cargo Increment Inst"

Also, with all this help I'm tempted to post the finished TOC procedure for more tweeking--it is still not very pretty in organizing sheet names on the TOC, but it does work...

 

[goblin]

I just did. What you posted was the shortened formula. I almost cut it in half, although part of it needs fixing, so a correct formula would be:

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

 

[itr674]

goblin - yea you were right... I forgot to change the +1 to a +6...

globin
em

 

[Legacy 12630]

Here's another way that makes the formula a bit shorter :-

1. Define two worksheet Names

Name : sh
Refers to : =Get.Document(76)

Name : wb
Refers to : =Get.Document(88)


2. To return the sheet name

=RIGHT(sh,LEN(sh)-LEN(wb)-2)


3. To return the sheet name without the first 5 characters

=RIGHT(sh,LEN(sh)-LEN(wb)-7)


4. To return the workbook and sheet name

=sh


5. To return the workbook name

with .xls extension
=wb

without .xls extension
=SUBSTITUTE(wb,".xls","")


WARNING
None of the above formulas, or any other formulas that use sh or wb, can be copied and pasted to other sheets or workbooks.
If copy/paste to other sheets is tried, Excel will crash.

Therefore, the method per above is not recommended if you want to distribute the workbook to other users.

 

[masim]

Give this a try....

Paste this in a normal module of ur workbook.

Function WS() As String
Application.Volatile
WS = ActiveSheet.Name
End Function

In cell A1 of every sheet u can have the following formula.
=ws()

Regards
Asim

 

[Legacy 12630]

Give this a try....

Paste this in a normal module of ur workbook.


In cell A1 of every sheet u can have the following formula.
=ws()

Function WS() As String
Application.Volatile
WS = ActiveSheet.Name
End Function

Regards
Asim

I misread the original post as specifiying that it was required to be done without using VBA - but that is not exactly what it says.

An alternative to Asim's simple solution is to put the following in the Workbook code module if the sheet name is required for every sheet, and then nothing needs to be input to the worksheets.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
[A1] = ActiveSheet.Name
End Sub

Or for a particular sheet, put in the sheet code module :-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[A1] = ActiveSheet.Name
End Sub



In the original post, what is the date in the message at the bottom of the post :-
[ This Message was edited by: em on 2003-02-09 17:31 ]

 

[itr674]

Anyone up out there that could give me revision of one of these formulas to just show the file name "only" in a cell?? No add-ins...

 

[iPaul]

Good stuff Aladin Akyurek - nice simple solution.

However, you could simply use the RIGHT formula in place of the MID formula, i.e. =RIGHT(A1,[number of characters in worksheet name])

Therefore, assuming the name of your worksheet is 'Sheet1' (Excel default) in your example above, you would have:

(1) In B1 enter:

=CELL("filename",A1)

(2) In A1 enter:

=RIGHT(B1,6)

or combined using only cell A1 it would be:

=RIGHT(CELL("filename",A1),6)

Important: No result will be displayed until the worksheet has been saved (at least once). This is only applicable for newly created unsaved workbooks.

 

[iPaul]

Anyone up out there that could give me revision of one of these formulas to just show the file name "only" in a cell?? No add-ins...

itr674 in Aladin Akyurek's example just use the first formula, i.e. =CELL("filename",A1).

However, this will also provide you with the sheet name as well. If you do not need this then also use the LEFT and LEN formulas, e.g. assuming that your worksheet name is Sheet1 and you are using the same cells in Aladin's example then:

=LEFT(B1,LEN(B1)-6)

or for one complete formula:

=LEFT(CELL("filename",A1),LEN(CELL("filename",A1))-6)