How to Lock a Cell Value When Status Changes to FILLED in Excel
Question details
The user needs a dynamic calculation based on the TODAY() function to stop updating and retain its last calculated number when a corresponding status cell is marked as 'FILLED'.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Tracking elapsed time or days dynamically and needing to permanently freeze the calculation count once a specific task is marked as completed or FILLED.
- Observed behavior
- The current formula (=$G$1-D2) constantly updates because cell G1 contains the volatile TODAY() function. The goal is to stop the update and keep the most recent numeric value (e.g., 95) when the cell in column C equals 'FILLED'.
To allow a formula to reference its own cell without triggering a circular reference error, you must first enable 'Iterative calculation' in your spreadsheet settings.
Use a Self-Referencing Formula with Iterative Calculation
By enabling iterative calculations, you can use an IF function that refers to its own cell. This allows the cell to hold its current value indefinitely once the condition is met.
A self-referencing formula checks the status cell. If the status is 'FILLED', it outputs its own current value (locking it in place). If it is not 'FILLED', it performs the standard date math calculation.
Navigate to File > Options > Formulas (or Menu > Options > Calculation in WPS Spreadsheet). Check the box for 'Enable iterative calculation' and click OK.
Select cell E2 and input the formula: =IF(C2="FILLED",E2,$G$1-D2). Press Enter to apply.
Click the small square at the bottom-right corner of cell E2 and drag the fill handle down to copy the formula to the rest of the column.
Manually Freeze Values Using Paste as Values
If you prefer not to change global calculation settings, you can manually replace the dynamic formula with its static value once the task is filled.
Clear the Result When the Status is FILLED
Use this alternative if you do not need to preserve the last count and instead want the cell to appear completely blank when the status becomes FILLED.
Easily Manage Complex Calculations with WPS Spreadsheet
WPS Office provides robust spreadsheet tools that seamlessly support iterative calculations, self-referencing formulas, and advanced conditional logic to streamline your task tracking and daily counts.
- 1. Open your tracking sheet: Launch WPS Spreadsheet and open the workbook where you are tracking your dynamic daily counts.
- 2. Access Calculation Options: Click on 'Menu' at the top left corner, select 'Options', and navigate to the 'Calculation' tab.
- 3. Enable iterations: Check the box for 'Iterative calculation' and click OK to allow self-referencing formulas to function properly.
- 4. Apply your IF formula: Enter the formula =IF(C2="FILLED",E2,$G$1-D2) directly into your target cell to freeze the required value.
Frequently Asked Questions
Why do I get a circular reference error when using the self-referencing formula?
A circular reference warning occurs because your formula in cell E2 is telling the spreadsheet to look at cell E2 for the answer. To resolve this, you must enable 'Iterative calculation' in your spreadsheet's formula options, which tells the software how many times to calculate the loop.
Will this formula update if I type 'filled' instead of 'FILLED'?
Yes. The IF function in Excel and WPS Spreadsheet is not case-sensitive by default. Typing 'filled', 'Filled', or 'FILLED' will all successfully trigger the condition to freeze the cell value.
Can I use VBA to automatically lock the cell instead of using iterative calculations?
Yes. You can use a Worksheet_Change macro in VBA to monitor column C. When the code detects 'FILLED' being entered, it can automatically replace the formula in column E with its current static value, completely avoiding circular references.
