Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Scott24x7 (Programmer) (OP)
30 Jul 06 21:44
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."hammer

Helpful Member!  Lilliabeth (TechnicalUser)
30 Jul 06 22:19
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?-

Helpful Member!  Scott24x7 (Programmer) (OP)
30 Jul 06 22:26
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."hammer

Lilliabeth (TechnicalUser)
30 Jul 06 23:57

Quote:

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 (Programmer) (OP)
31 Jul 06 0:31
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."hammer

Lilliabeth (TechnicalUser)
31 Jul 06 0:59

Quote:

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 (Programmer) (OP)
31 Jul 06 1:27
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."hammer

Lilliabeth (TechnicalUser)
31 Jul 06 1:51

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 (TechnicalUser)
31 Jul 06 2:07
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 (Programmer) (OP)
31 Jul 06 2:47
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."hammer

Lilliabeth (TechnicalUser)
31 Jul 06 6:17


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 (Programmer) (OP)
31 Jul 06 7:39
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."hammer

firefytr (TechnicalUser)
31 Jul 06 11:55
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 (TechnicalUser)
31 Jul 06 13:45
Hi,

use the following combination of what Liliabeth gave you:

CODE

=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 (TechnicalUser)
31 Jul 06 15:00
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 (Programmer) (OP)
31 Jul 06 22:20
Rofeu,
   When I use this:

CODE

=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."hammer

Rofeu (TechnicalUser)
1 Aug 06 5:38
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 (TechnicalUser)
1 Aug 06 6:01
Hi,

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

CODE

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 (IS/IT--Management)
1 Aug 06 6:31
This any use to you  ?

CODE

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 (TechnicalUser)
1 Aug 06 9:38
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

Scott24x7 (Programmer) (OP)
2 Aug 06 3:05
Appreciate all the help everyone... but I've abandoned the idea of having a single dynamic cell that captures the name of a sheet, and serves as the hyperlink dynamically.  I have read and re-read about a dozen times the help file on this, used your examples, and I still can't get this to work.  If I have to set they hyperlinks manually, I guess that's what I'll have to do.

Incidentally, I could not even get the =HYPERLINK() function to work at all, to actually take me to a specified cell on a nother page, which I found really disappointing.  I even copied the example out of help, set up the same pages, and it didn't work.  So, I'll stick with what I've got for now...

Best Regards,
Scott

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

firefytr (TechnicalUser)
2 Aug 06 10:10
How about a table of contents...

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

Regards,
Zack Barresse

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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close