Auto Populate Multiple Fields Based On Source Cell Data/Change

FreshHits

New Member
Joined
May 3, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hey folks,

Have been searching the web and this forum for the last couple of days, but can't seem to find the answer to my specific question.

I have a workbook that contains sheets named as follows - Data, Monthly Rolling, Sheet1, Sheet2...Sheet31

This workbook has been designed as a template (not saved as template file format, however), and covers data entry such that the workbook will last an entire calendar month.

What I am trying to achieve:

I am trying to get the workbook to auto-populate dates across all worksheets in the date format dd-mmm-yy, with the source cell Sheet1!A2, and dates populated into subsequent A2 cells in the remaining worksheets. From there, I am wanting the sheet names to be renamed with the dates located in each worksheets A2 cell, with the date format dd-mmm-yy.

If the user mistakenly enters the wrong date into Sheet1!A2 (for instance, the incorrect month) then all the data would be populated across the other worksheets and the worksheets would be named respectively - however, when immediately reentering the correct date, all the required data is updated and renamed immediately. This would require the code to not only ignore the first two sheets in the workbook (Data, Monthly Rolling), but to also recognize the 3rd sheet (Sheet1) as the starting sheet for the date sequence/sheet name date sequence, regardless of what the sheet name currently is. If however data is entered into the source cell (Sheet1!A2) that is NOT a date format, the code simply doesn't operate and no data is populated, with an error message/pop up that states something like "The data entered into this cell ([insert sheet name of "sheet1"], cell A2) is not a date. Please enter a date.".

For months that have <31 days, the 'left over' days/sheets in the workbook can be renamed to something like, "Do Not Use" or "Not Dated", with the respective A2 cells either left blank, or filled with some other not-specific text, such as "No Date".


The issue I am finding is that I have not been able to find code that does this, and my attempts at modifying existing code to tailor this has been futile. As I am wanting it to update in real-time (and not required saving/closing/reopening the workbook), I am having a super hard time trying to figure this out.

Hopefully that is clear enough :) any assistance/guidance on this would be greatly appreciated!

Many thanks,

FreshHits
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Just giving this one a bump :)

Have tried a few more time to write my own code, but still struggling to get anything working.

Any help would be stellar, thanks so much!
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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

Which adblocker are you using?

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
Back
Top