Hi, in my honest opinion you can only do this using VBA (a macro).

This macro could be triggered by a worksheet_change event or else by a button which you could place in the QAT ribbon to do that for the active sheet and only checking the cell with the text date

You should also mention the version of Excel you're using.

You could create the basic macro by recording one and then edit the macro to become dynamic 

Hi Hans Hallebeek,

Thank you for your answer! I am currently using Microsoft 365 Enterprise Version 2407. I don't have a problem with using macros but I am not sure how to set that up. What would the code be for a worksheet_change event for the macro?

Thank you,

Lisa Donaldson

In the sheet's project code Worksheet_Change event.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E1")) Is Nothing Then
MsgBox Range("E1").Text
End If
End Sub

Instead of MsbBox your code here

Hi Hans,

I get a runtime error when the script is being ran. I used the debugger and here are the results. There will always be something in that field. I have a generic file that I use and then just save it for the next month and update the dates and so on. With the code that I put in there is that correct?

Thank you

Without the file I cannot tell, but the first thing YOU can do is check if the referred sheet "Sept 9th" exists.

You cannot select a non-existing sheet.

VBA is very smart and at the same time very dumb. It does exactly what YOU tell it to do but cannot correct itself, therefore you use on Error events.

Debugging (troubleshooting) is what takes time.

VBA reads like a book and as a book it reads line code by line code.

You do that and think of what you're reading and check if the action is valid.

Hi Hans,

So "Sept 9th" did not exist any more, so that is where that error came from but is there a way that I can have it update it no matter what name it is when I change the dates in "TheData" sheet in cell E9? What would be the easiest way to make it do that?

Thank you! 

Just to be clear about this. If it's the active sheet you want to rename you can just rename it with the macro

ActiveSheet.Name = "ten new name here" of course respecting the correct syntax for a sheet name, like no special characters and shorter than 32 characters.

You will, at ALL times first have to check if there is no other worksheet with the same name.

Again, without a sample file it's all just telling you what you could do, and the rest is limited to your own creativity since VBA programming is nothing more than that; your creativity and imagination respecting VBA coding syntax

I appreciate your responses Hans!

I don't want to put the document on this, but I can share pictures. 

Essentially what I am trying to accomplish is when I change the date on "The Data" sheet because the month changes I am wanting it to also change the date or "Name of the sheet" dynamically to the contents. This will never be blank because I will be reusing and only changing the dates that are on "The Data" sheet. But I am fine with it pulling the data from cell E1 on each sheet. Example according to picture "Sept 30th" sheet pulling the "changed date from cell E1 in "Sept 30th" sheet after it was changed on "The Data" sheet.

So a real circumstance would be... changed date on "The Data" sheet to November dates. I am wanting it to automatically update the date on all the sheets that are in cell "E1" and then update the date and take "what ever" dates might have previously been the name of the sheet, would then be replaced by the new date or "new sheet name"

What would be the proper coding to achieve this?

Hi Lisa,

The proper code would be something lika that is triggered the moment to change the contents of the cell with the data.

This macro (that's the only way it's going to work) will have do several things:
1. 'translate' that date to the correct syntax so that it's accepted as sheet name
2. check if the name is not already used in any of the other worksheets
3. Rename the sheet's name

I don't know what the policy is for the files you use, but no macros are allowed you will have to either place all the macros in your Personal macrobook or else create an entirely new file that will be used as an AddIn so that's it's available at all times that does the handling of these changes.

I would go for the last option since you can control it better and it might be foolproof.

I don't know if I've explained myself but that would be my approach.

If you do not wish to post your file; you could always PM me and I'll give you my email address 

Hans,

I am not sure how to PM you on this.

So is there not a macro that would just pull and show the data from "The Data"  sheet? If I have to type in the date that would defeat the purpose for me. Unless that is not what you are saying.

There is a macro, but you will have to write it.

You can start by recording a macro.

1. Start the Macro Recorder

2. Go to the Sheet you want to update

3. Change the date in the cell

4. Double click the sheets tab and type the new name

5. stop the macro recorder

Now open the VBA editor an look at the macro that has been generated, you can paste that as text in a post and I'll try and explain how to edit it.

Sub Changedate()
'
' Changedate Macro
'

'
Range("E1").Select
ActiveCell.FormulaR1C1 = "='The Data'!R[25]C"
Range("E1").Select
Sheets("Sept 30th").Select
Sheets("Sept 30th").Name = "28-Nov"
End Sub

Here is the code it produced

Okay, let me play with this, I'll use the image files to vision your file and offer you a possible solution.

Just to make sure, the data you enter is formatted as a date, Correct?

Just an update, I've not had much time yet but still have it on my ToDo list