How to Save Daily Dashboard Values When the Date Changes
Question details
The user wants to automatically store the final daily results of formulas (such as COUNTIFS) on a dashboard before the date changes to preserve historical data.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Tracking daily metrics on a dashboard where formula outputs update dynamically, meaning past daily values are lost unless manually recorded.
- Observed behavior
- Standard formulas recalculate continuously. Without manual intervention, yesterday's data is overwritten by today's metrics, failing to build a historical log.
Ensure you have created a dedicated 'History' or 'Log' worksheet within your workbook with headers for 'Date' and the specific dashboard values you wish to record.
Automate Value Logging Using a VBA Macro
Because standard formulas cannot freeze historical values, using a VBA macro is the most reliable way to copy daily dashboard results and append them as static values to a historical log sheet.
This method involves writing a short script that copies the current values of your dashboard and pastes them into the first available blank row on your History sheet.
Press ALT + F11 on your keyboard to open the Visual Basic Editor in your spreadsheet application.
In the top menu, click on Insert, then select Module to create a blank workspace for your macro code.
Paste your macro script. The code should target your dashboard cells (e.g., Sheets("Dashboard").Range("B2").Copy) and paste them to the next empty row in your log sheet using PasteSpecial Paste:=xlPasteValues.
You can either insert a button via the Developer tab (Insert > Form Controls > Button) and assign this macro to it for manual daily clicks, or use the Workbook_Open event to check the date and run it automatically.
Use Iterative Calculation (Advanced Formula Method)
If VBA is strictly prohibited in your environment, you can use circular references with Iterative Calculation enabled to 'lock' a value once a specific date matches.
Automate Daily Reports Seamlessly with WPS Spreadsheet
WPS Spreadsheet fully supports VBA macros, allowing you to easily automate your daily dashboard logging. You can track, analyze, and record your daily COUNTIFS results automatically without losing any historical data.
- 1. Open your dashboard file: Launch WPS Spreadsheet and open your existing dashboard file containing the COUNTIFS formulas.
- 2. Access the VBA Editor: Navigate to the 'Tools' tab on the top ribbon and click on 'Macros' > 'Visual Basic Editor', or simply press ALT + F11.
- 3. Insert the Automation Script: Right-click your workbook in the Project pane, select Insert > Module, and paste your daily value-logging VBA code.
- 4. Save and Execute: Save the document as a Macro-Enabled Workbook (.xlsm) and run the macro to instantly log your historical daily values.
Frequently Asked Questions
Can I save daily values without using VBA or macros?
Yes, but it requires manual effort. You will need to copy the daily results from your dashboard and use 'Paste Special > Values' into a historical log sheet before the source data changes for the next day. Standard formulas alone cannot freeze historical data.
Why do my COUNTIFS formulas change every day instead of saving?
Formulas are strictly dynamic. Whenever the underlying source data changes, or if your formula relies on time-sensitive functions like TODAY(), the formula recalculates instantly. It does not possess a 'memory' to store what the value was yesterday.
How do I trigger the macro automatically at midnight?
You can use the 'Application.OnTime' method in VBA to schedule a macro to run at a specific time, but the workbook must be open. Alternatively, you can use the Windows Task Scheduler to run a VBScript that opens your workbook and triggers the macro at midnight without your active intervention.
