# Change workshet name in Cell

#### sccson

##### New Member
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

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Joe4

##### MrExcel MVP, Junior Admin
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

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
=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

##### New Member
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

Replies
8
Views
402
Replies
2
Views
178
Replies
6
Views
548
Replies
2
Views
211
Replies
3
Views
255

Threads
1,195,702
Messages
6,011,200
Members
441,594
Latest member
AVO

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back