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

 

[Aladin Akyurek]

On 2002-07-07 07:22, em wrote:
How can I get the sheet tab name to show in cell A1 without having to RUN a macro?

In B1 enter:

(1)

=CELL("filename",A1)

In A1 enter:

(2)

=MID(A1,SEARCH("]",A1)+1,LEN(A1)-SEARCH("]",A1))

You can substitute CELL("filename",A1) for A1 in (2), if so desired, to reduce the whole thing to a single formula.

An alternative would be

=SHEETNAME()

that uses a UDF from the Morefunc add-in, freely downloadable at:

http://longre.free.fr/english/index.html

 

[itr674]

When I add the add-in function is that attached to the file? In other words will anyone who opens this on another computer see the tab name?

 

[Aladin Akyurek]

On 2002-07-07 08:29, em wrote:
When I add the add-in function is that attached to the file? In other words will anyone who opens this on another computer see the tab name?

No. All users must add the add-in. Since that's inconvenient, you'd better use the formulas with built-in functions.

Aladin

 

[itr674]

Rogggg--I used the formula and put it together in one--it works perfectly--THANKS.

If you have time some day--could you explain how it works ...

 

[tusharm]

Put the CELL function in a cell by itself and see the result.

Then, put the SEARCH function in a cell by itself and look at the result.

Then, look up XL help for SEARCH, LEN, MID, and CELL.

You'll have the answer to 'how it works!'

 

[itr674]

This is so much fun ...

 

[itr674]

OK, what am I doing wrong!!!!!

I put =CELL("filename",A1) in B1 and nothing shows up!!!!!!

NEVER MIND -- NOW IT IS ALL WORKING,....

 

[itr674]

Using the below this formula to put sheet name in A1, but sheet names all have a 5 alpha-numeric prefix and I do not want that part of the sheet name. I have been playing with LEFT but haven't had any luck????

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

 

[Aladin Akyurek]

Using the below this formula to put sheet name in A1, but sheet names all have a 5 alpha-numeric prefix and I do not want that part of the sheet name. I have been playing with LEFT but haven't had any luck????

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

A1:

=CELL("filename",A1)

B1:

=RIGHT(A1,LEN(A1)-SEARCH(CHAR(127),SUBSTITUTE(A1,"]",CHAR(127),LEN(A1)-LEN(SUBSTITUTE(A1,"]","")))))