求助：Excel满足指定条件后停止公式自动更新的实现方法

Hey there, let's work through this problem to get your C1 cell to stop updating once an invoice date is entered in D1. First, I spotted a small issue in your original formula: you're referencing $C1 in the NETWORKDAYS function, which creates a circular reference. Let's fix that first, then add the stop-updating logic.

Option 1: Use Iterative Calculation (No VBA Needed) #

This method uses Excel's iterative setting to let the cell retain its value once the condition is met.

1. Fix the base formula for C1 first. Replace your current formula with this corrected version (we'll add the D1 check next):

   =IF(B1="","-",IF(NETWORKDAYS($B1,$A$1)>0,NETWORKDAYS($B1,$A$1),"-"))
   

   This properly calculates the working days between the received date (B1) and today (A1) when B1 is filled.

2. Add the D1 condition to lock the value when an invoice date is present. Update the formula to:

   =IF(D1<>"",C1,IF(B1="","-",IF(NETWORKDAYS($B1,$A$1)>0,NETWORKDAYS($B1,$A$1),"-")))
   

   Here's how it works:

   • If D1 has an invoice date (D1<>""), the cell keeps its current value (C1) instead of recalculating.
   • If D1 is empty, it uses the original logic to calculate the open days.

3. Enable iterative calculation in Excel:

   • Go to File > Options > Formula
   • Check the box for Enable iterative calculation
   • Set Maximum Iterations to 1 (this prevents unnecessary recalculations)

Option 2: Use VBA for Permanent Value Locking (More Reliable) #

If you want to convert the formula to a static value permanently once D1 is filled, a simple VBA macro will do the trick. This avoids any reliance on iterative settings.

1. Open the VBA Editor:

   • Right-click your worksheet tab (e.g., "Sheet1") and select View Code

2. Paste this code:

   Private Sub Worksheet_Change(ByVal Target As Range)
       ' Trigger when any cell in column D is edited
       If Not Intersect(Target, Me.Range("D:D")) Is Nothing Then
           Dim cell As Range
           For Each cell In Intersect(Target, Me.Range("D:D"))
               ' If D cell has a value and C cell has a formula, convert C to static value
               If cell.Value <> "" And Me.Range("C" & cell.Row).HasFormula Then
                   Me.Range("C" & cell.Row).Value = Me.Range("C" & cell.Row).Value
               End If
           Next cell
       End If
   End Sub
   

3. Save your file:

   • Save the workbook as a Macro-Enabled Workbook (.xlsm) to keep the VBA code active.

Pros & Cons of Each Method #

• Iterative Calculation: Quick setup, no coding required, but relies on Excel settings and keeps the cell as a formula (it just stops recalculating).
• VBA: Permanently converts the cell to a static value, no ongoing settings needed, but requires enabling macros and saving as .xlsm.

内容的提问来源于stack exchange，提问作者D Beech