Change workshet name in Cell

[sccson]

I have a workbook with 35 - 40 sheets - the total from those individual sheets gets put into a summary sheet. The problem is, I need to name each sheet with a persons name and have their total in a summary sheet. How can I change the name on the summary sheet (cell as well as the total) if the name of the original sheet changes.

Thanks

Mike

 

[Joe4]

What is the formula?

If it is simply a direct range reference, Excel seems to update it automatically for you.

For example, if you have two sheets, Sheet1 and Sheet2, and on sheet 2 you have the formula:
=Sheet1!A1
and you change the name of Sheet1 to Dog, Excel is smart enough to change your formula to:
=Dog!A1

 

[Oaktree]

Not sure I understand.

If you have a formula =Sheet1!A1 and you change Sheet1's name to SomeonesName, the formula (formerly =Sheet1!A1) will automatically change to be =SomeonesName!A1.

 

[sccson]

Actually, I should have said: I have a column with names, the names are sheet1, sheet2,.... I need the names to change

Patients Total
Sheet 1 0
Sheet2 5

If sheet 1 changes to John Doe, I need the Patients column to reflect that as well as having the Totals column reflect a change if any.

Sorry about that.

Mike

 

[Oaktree]

=RIGHT(CELL("filename",Sheet1!A1),LEN(CELL("filename",Sheet1!A1))-SEARCH("]",CELL("filename",Sheet1!A1)))

Will return the name of Sheet1 and will automatically update if you change the name of Sheet1.

The totals column, assuming it's formula driven, should update per jmiskey and my previous posts.

 

[sccson]

Works great thank you! Now, is there a way to have it autofill for 35 sheets? I tried grabbing the corner for autofill, but it changes the value of Sheet1!A1 to Sheet1!A2, Sheet1!A3, etc. instead of changing, Sheet1!A1, Sheet2!A1, Sheet3!A1


Thank you