Return the tab name of any cell.

[BrianSch]

thread68-1260610

This is in reference to a thread where a user was looking to return the tab name of a cell on another worksheet. I had the same question and came up with this formula, which should work in any worksheet or excel file:

=MID(CELL("address",A1),FIND("]",CELL("address",A1),1)+3,FIND("$",CELL("address",A1),1)-FIND("]",CELL("address",A1),1)-4)

Whatever the cell is, replace A1 with it's location.

Good luck!!

 

[SkipVought]

Hi,

Is there a question in there?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BrianSch]

Yes, the link to the question is near the top just below my name. The original question was:

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?

Also, I realized a slight miscalculation in my formula. The correct formula is:

=MID(CELL("address",A1),FIND("]",CELL("address",A1),1)+1,FIND("$",CELL("address",A1),1)-FIND("]",CELL("address",A1),1)-3)

The way it works is by identifying the positions of the key characters which excel places on either side of the tab name within the cells address. It then takes the difference between the two numbers to figure out how many characters to display, which are the number of characters in the tab name.

 

[SkipVought]

????

Threads are to be answered in their own thread.

YOUR thread appears to have no question??? That's not the way this forum works. Maybe you should spend some time observing what goes on here before doing something inappropriate.


How do I show the sheet name / filename in a cell faq68-2561

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[softhemc]

Skip

That earlier thread is marked as closed and one would assume that to extend or follow up on the question the OP has followed correct practice by creating a new thread and referring back to the closed thread.

 

[SkipVought]

Actually the sticking point was not in obtaining the sheet name, but in also creating a hyperlink automatically. THAT was the unanswered question.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

There is also an FAQ on usage of the CELL function for sheet / workbook names:

http://www.tek-tips.com/faqs.cfm?fid=2561

Thank you for posting however - it is always good to have people around that want to contribute

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[strongm]

The (minor) problem with the FAQ is that it doesn't work if the workbook has not been saved.

Here's a possible candidate for a hyperlink that'll dynamically update itself as and when Sheet2's name changes

=HYPERLINK(CELL("address",Sheet2!A1),MID(LEFT(CELL("address",Sheet2!A1),FIND("!",CELL("address",Sheet2!A1))-1), FIND("]",CELL("address",Sheet2!A1))+1,255))