Excel Sheet Tab Names 2

[Scott24x7]

All,
Is there a way (via formula) to get the name from a sheet tab for use as a cell in another page? (Something like referencing a cell, with ='Sheet1'!A5) but to get the name off the sheet tab itself?

Many thanks and


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Lilliabeth]

I'm not entirely sure I understand the question, but if you want to display the sheet name in a cell..

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


(Won't work in a file that has never been saved.)

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[Scott24x7]

That's close to what I'm looking for I think... I tried this, and it gives me the name of the page I'm on, but don't know how I can use it to get the name of other sheets.

Here's what I'm trying to accomplish:

I have a spread sheet with several sheets in it. Sometimes there are more, and sometimes there are less.

This is Cost Reporting (budget type stuff) for projects, so sometimes the sheet names change for one client or another, or project. (You might have Furniture in one project, but not in another, and instead of Server Cabinets, something like that...)

Rather than having to change 3 cells in seperate pages, I want to be able to just take the name from the page to show what that page contains, which I have links to in my summary. (I have set up hyperlinks into the pages from the summary page, so all you have to do is click on it. These are the "Page Names" right now, and if I can get them to display there, then any time I change a page name, it will be reflected properly in my hyperlink in my summary page, and I don't have to keep updating it too.)

Any help?


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Lilliabeth]

don't know how I can use it to get the name of other sheets.

Replace A1 with Sheetname!A1.

Or, you could put the formula in each sheet, and then in the summary sheet create a formula that refers to them - =Sheetname!A1
=Othersheetname2!A1

I don't feel like I really understand the question... you might want to have a look at the INDIRECT function.

YOu can also use a macro... see this page

http://exceltips.vitalnews.com/Pages/T0131_Deriving_the_Worksheet_Name.html

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[Scott24x7]

Lilliabeth,
Thanks for the help so far, I'm getting closer... let me explain my problem again, and see if I can do a better job.

Objective: Retrieve the name of sheets in the workbook, for use in a summary page.

Reason: Enter the name of the page once, and have it appear in other pages without having to go change them there too. (One entry, updates mulitiple cells on other pages).

Primary Use: On my summary page, it will automatcially display the names of the other pages in my workbook.

I'm looking for a "forumla" way that does not force me to specify the name of the page, or that does not solve my problem, as I have to go change the references then.

So, for example sheet1 is named "Summary". It has 15 rows that correspond to 15 other sheets. Sheet4 is normally 'Cabling' but in this case, my client wants it to be 'Structured Cabling'.

I have a cell in my summary page called 'Title' that column contains the name of every page as a row, and in this case it is row4 (matches to sheet4).

If I go change the name of my sheet to "Structure Cabling" instead of "Cabling", I want the name to reflect correctly back in my Summary sheet as "Structured Cabling" as well, instead of just "Cabling". I don't want ot have to change cell references every time. If I take the advice of what you just suggested, if I'm understanding it correctly, I'd have to make:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
this:
=MID(CELL("filename",Cabling!A1,FIND("]",CELL("filename",Cabling!A1))+1,255)

to get it to work... but as soon as I change the sheet name to "Structured Cabling" this fails, because that sheet in Cabling!A1 no longer exists...

I'm trying to find something that is "Name Independant" so that I can retrieve the sheet name without needing to know it from another sheet.

What I've then done is put a hyperlink on each of the cells that contains the sheet names so that my user can just click there, and go to the page, rather than scrolling through 15 tabs to get to the one they want. (Its really handy, and they love it, but I want to make it more robust so that I don't have to do so much editing everytime someone decides they want the sheet name changed.... I'm assuming Excel won't pick this up automatically....)

Is that explained any better?


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Lilliabeth]

to get it to work... but as soon as I change the sheet name to "Structured Cabling" this fails, because that sheet in Cabling!A1 no longer exists...

Hmmm, are you sure? It should change. Please try it.

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[Scott24x7]

Lilliabeth,
Hey... you are right! The only thing it is not maintainting dynamically are my hyperlinks... So I have to go set those, which is a little difficult/annoying, but it is maintaining the other areas. That's a big help. I can now just change the page name and update they links, and it works. Many thanks for that! Will save me a good hour at least in set up time everytime I get a new client.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Lilliabeth]

Assuming you have
=StructuredCabling!A1
in cell A1 of the Summary sheet, put this in cell B1 of the Summary sheet:

=HYPERLINK("#"&A1&"!A1",A1)

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[Lilliabeth]

Or just put this in cell A1 of the Summary Sheet:

=HYPERLINK("#"&StructuredCabling!A1&"!A1",StructuredCabling!A1)

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[Scott24x7]

Lillibeth,
Ok... this is heaps of fun, but I can't get your "next phase" t work quite right... if this works the way I think you are suggesting, it will solve all my problems, (and be very cool at the same time).

In C9 of my summary I have:

=MID(CELL("filename",'Structured Cabling'!A1),FIND("]",CELL("filename",'Structured Cabling'!A1))+1,255)

When I put this in C9 instead:

=HYPERLINK("#"&StructuredCabling!A1&"!A1",StructuredCabling!A1)

I just get a #REF hyperlink that if I click goes no where... I think what you are trying to achieve for me is to get the sheetname and the hyperlink reference in the same cell, and that would be SPECTACULAR. The one other thing is, I want to make the active cell A9 on the page that I go to (Puts it under my headers then, and in the first active cell of the sheet). Can that be done???


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Lilliabeth]

Put this in cell A1 of StructuredCabling:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

and

this somewhere in Summary:
=HYPERLINK("#"&StructuredCabling!A1&"!A1",StructuredCabling!A1)

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[Scott24x7]

Lilliabeth,
That won't work, unfortuantly. I need to keep this as one cell on the summary page... But I appreciate all the help. If I have to set the Hyperlink's later manually, at least I only have to do it in one place.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[firefytr]

The VBA link Lillabeth gave is defunct and should NOT be used at all. It may appear that it gives correct results, but when used it does not.

http://vbaexpress.com/kb/getarticle.php?kb_id=213

Also check out these related links...

http://vbaexpress.com/kb/getarticle.php?kb_id=402

(for the name, replace "Index" with "Name" in this function)

http://vbaexpress.com/kb/getarticle.php?kb_id=120

http://vbaexpress.com/kb/getarticle.php?kb_id=401

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Rofeu]

Hi,

use the following combination of what Liliabeth gave you:

=HYPERLINK(CELL("address",SomeSheet!A1),MID(CELL("filename",SomeSheet!A1),FIND("]",CELL("filename",SomeSheet!A1))+1,255))

I think this does what you want.

Cheers,

Roel

 

[firefytr]

NB: The length for a sheet name is not 255...

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Scott24x7]

Rofeu,
When I use this:

=HYPERLINK(CELL("address",'Structured Cabling'!A9),MID(CELL("filename",'Structured Cabling'!A1),FIND("]",CELL("filename",'Structured Cabling'!A1))+1,255))

The link "Structure Cabling" shows up just like I want it. But when I click that link, I get an error that says "Can not find the specified file".



Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Rofeu]

Hi,

the problem is the space in your sheetname. If you replace it with say an underscore it'll work.

I'll see if I can find a way to build the actual string HYPERLINK expects when there is a space in the name later on.

firefytr: I just copied Liliabeth's formula there. Indeed it's not 255, but it'll work and it saves some extra formulae to determine the actual length.

Cheers,

Roel

 

[Rofeu]

Hi,

ok, build the part of the link in HYPERLINK as follows:

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CELL("address",Structured_Cabling!A9),"]","]'"),"'[","["),"!","'!"),"''","'")

Very ugly solution, but it'll work for sheets that have spaces as well as ones that do not.

If someone has a more elegant way of doing it: Please do post it!

Cheers,

Roel

 

[Chance1234]

This any use to you ?

Function SheetName(rAnyCell)
    Application.Volatile
    SheetName = rAnyCell.Parent.Name
End Function

Put this in a module, and then in a cell type

=sheetname(A1) to return the sheet name you are on,

or =sheetname(sheet2!A1) would return the name oif the second sheet.

Chance,

Filmmaker, gentleman and Legal champion of the small people.

 

[firefytr]

Chance1234, you must not have even read my post..

If you wanted to break the formula down some, you could put some of the variables in other cells if desired.

Fwiw, there is only a 31 character limit for sheet names.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci