Cell Name = Sheet Name (Yes I have searched)

[EBJBCB]

Hello everyone... Here is what I have

The first sheet is titled Totals - on this sheet I have a summary sheet of the data from the other sheets in the workbook. Cells A2 - A36 are 5 weeks worth of dates in text format (ie 10-1-11).

Each daily sheet has the sheet name in G2 as:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

so to pull the "text" version of the date into that cell.

What I would like to have happen is at the end of the month someone can go into the Totals sheet, change all of the "text" dates to next month and it automatically change the sheet names then g2 on each sheet would see the name change and update as well. There are already 35 sheets besides the total sheets. Each day has a daily sheet, thus 5 weeks worth. Any help would be greatly appreciated. Thanks!!!

 

[Saagar]

Here is a solution. For this to work, you will need to use VB code and use column B in "totals" sheet to provide new sheet names. Make sure cells B2 to B36 are blank.

Right click on the "totals" tab and select "view code" option. When the VB code window opens, paste this code as is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2:B36")) Is Nothing Then
    If Not Trim(Target.Value) = "" Then
        sheetname = CStr(Cells(Target.Row, Target.Column - 1))
        Worksheets(sheetname).Name = Target.Value
        Cells(Target.Row, Target.Column - 1) = Target.Value
        Target.Value = ""
    End If
End If
End Sub

Now, any time you enter a new name in cells B2 to B36, the code will change the name of the sheet in the corresponding row in column A, it will also replace the new name in column A and empty the value entered in column B.

Try it out and let me know if it meets your needs.