I’m working on a spreadsheet with three data validation dropdowns for Year, Month, and Week in cells A5, B5, and C5. I want these to automatically pick the current date info when someone opens the file. I’ve looked everywhere online but can’t find a solution. Does anyone know how to make Excel do this? It would save me a lot of time not having to manually update it every time. Maybe there’s a formula or VBA code that could help? I’m not great with Excel so any tips would be awesome. Thanks!
Hey there, I’ve got a trick that might help you out. I use this in my work all the time. Instead of data validation, try using formulas directly in the cells. Put =YEAR(TODAY()) in A5, =TEXT(TODAY(),“mmmm”) in B5, and =WEEKNUM(TODAY()) in C5. This way, they’ll update automatically every time the workbook is opened or recalculated.
If you still want dropdown options, you can create named ranges with these formulas and use them in data validation. It’s a bit more complex, but it gives you more flexibility. I’ve found this method to be pretty reliable across different Excel versions.
Just remember, these formulas use the system date. So if someone’s computer date is off, it might show incorrect info. But for most cases, it works like a charm. Hope this helps!
I’ve tackled a similar issue before, and here’s what worked for me:
For the Year in A5, use this formula in the data validation: =YEAR(TODAY())
For Month in B5: =MONTH(TODAY())
For Week in C5: =WEEKNUM(TODAY())
Set these as the default values in your data validation dropdowns. This way, they’ll update automatically when the file is opened. No VBA needed.
One caveat: the values only update when the file is opened or recalculated. If you need real-time updates, you might need to explore more complex solutions involving macros or Power Query. But for most purposes, this simple approach should suffice.
hey, try a vba macro on open. use auto_open() to set A5=Year(Date), B5=Month(Date) & C5=WeekNum(Date). works n updates automatically