Need to Name Cells on Multiple Sheets According to Tab Name

[sb212410]

I have a large amount of worksheets that can be accessed via a summary sheet that used to use the HYPERLINK function to hyperlink to the proper tab.

The problem at hand is that the named cells where created by using the original worksheet name and therefore I could create hyperlinks by simply pulling the worksheet name. Now the named cell's and the Worksheet name which they rely on have become disconnected. Is there any way to go through each worksheet in my workbook and give a particular cell (w1) the name of my worksheet? That way when the worksheet name changes I can rerun this macro and all named cells will be updated. Is this possible?

the logic I am looking for in leymans terms is:

For each worksheet in my workbook, If the worksheet name does not equal "Summary" Then
I want to give cell w1 the same name as the worksheet's name

Thanks in advanced

[FDibbins]

Not sure this really answers your question, but this suggestion will create a list of sheet names and give each a hyperlink to cell A1 on each sheet. As you add/delete/rename sheets, this will update the list (it is not automatic tough)

Create a range name (I called mine Sheetnames)
put this in the Refers to: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
To get a list of sheet names, put this in a cell and copy down (started in A2)...
=IFERROR(INDEX(Sheetnames,ROWS($A$2:A2)),"")

If you want to be able to click the cell and go to that worksheet, change that formula to this...
=IFERROR(HYPERLINK("#"&"'"&INDEX(Sheetnames,ROWS($A$2:A2))&"'!A1",INDEX(Sheetnames,ROWS($A$2:A2))),"")

[Mumps1]

Try:

Please Login or Register  to view this content.

[FDibbins]

Thanks for the feedback