Linking to Tab Name

[J G]

I entered my 34 tab names in a range & give them a name 'TabList' to use in a formula. Changing the names of the tabs breaks the formula. Is there a way to link a cell to a tab name?

Thanks,

 

[Xiq]

How about a little table with a few formulas for "linking" the tabs and then extract the name? See attached file for example.

In A2:

=CELL("address",Sheet2!$A$1)

In B2:

=MID(A2,FIND("]",A2)+1,FIND("$",A2)-1-FIND("]",A2))

 

[J G]

This is amazing. I really thought this would be impossible. Thank you so much!

 

[Abhijeet R. Joshi]

Hi JG,

You can also follow the below procedure:
1. use the formula =MID(CELL("filename"),FIND("]",CELL("filename"),1)+1,200) in any cell of each sheet which will always stay free.
2. Link those cells to the cells on your table...

Hope this helps..

 

[bobhc]

Good day JG

With Xig's formulas all you need to do if you change a sheet name is press the F9 key

 

[J G]

Thanks Abhijeet. I'll look into your solution too.

To use Xiq's solution I pair that formula with the substitute formula to take out the "'!" These extra characters were causing ref error.

 

[Xiq]

You can also just do a little edit to the formula:
=MID(A2,FIND("]",A2)+1,FIND("$",A2)-2-FIND("]",A2))

 

[J G]

Thank you. I'm starting to understand. I don't think I've ever used the Mid or find formulas before.

I don't seem to have to press F9 like bob says. My sheet is updating automatically.

 

[SirJB7]

@J G
Hi!
Actually it isn't required... except for my friend b(ut)ob(ut)hc's IBM PC XT 4.77 MHz with 2 5.25" 320 Kb (he'll be soon upgrading to 460) floppy drives and green phosfore Hercules monitor.
Regards!