Dashboard refresh
My sheet gets data from a form and update requests, it rarely needs to be opened directly.
I have reports for open items and completed items. I have summary fields in the sheet that count and calculate late items.
The Dashboard has metric widgets that look at the summary fields and two report widgets.
The dashboard doesn't show current info unless I've open the sheet that day. Is that normal?
~ Linda
Best Answer
-
Ok. Set up two separate automations. The first one will lock the rows at 1am and the second will unlock the rows at 2am. The lock/unlock should update the TODAY function without you having to manually activate the sheet.
Answers
-
I am going to assume that some of your calculations also include the TODAY function. The TODAY function only updates when the sheet is "activated". There are a number of ways to activate a sheet to include opening it directly, form submission, update requests, cell link updates, and certain automations.
There are a number of solutions for this, but the best one will depend on some details?
How many different sheets do you use the TODAY function on?
-
There's only one sheet driving the reports and dashboard. All of the summary fields (and conditional formatting) that calculate late use Today().
-
Also, the reminder automation emails for late items are firing successfully.
-
Is there a particular hour each day that the sheet containing the TODAY function can be "locked"? Such as from 1am - 2am or something like that?
-
Yes. Anytime after hours.
-
Ok. Set up two separate automations. The first one will lock the rows at 1am and the second will unlock the rows at 2am. The lock/unlock should update the TODAY function without you having to manually activate the sheet.
-
Awesome, thanks! I'll try it tonight.
-
Definitely let me know how it goes!
-
Thank you! It worked. I'm so glad my mostly hands off sheet can stay that way.
-
Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!
