Home/ Questions/Q 14521
Next
Answered
computing
  • 1
Asked: 2021-12-15T03:38:21+00:00 In:

How Do Store Calculated Value As Static Entry

  • 1

In Excel 2010 I have a cell that looks-up a calculated valu based on the values in 3 other cells.
I want that to be store as static so that when the date changes it does not change.
The code I have at the moment calculates the value correctly but when I change the date It recalculates

=COUNTIF(Stage!$H$2:$H$3000,B6&$D$1&$E$1)

Stage!$H$2:$H$3000 contains a calculated entry based on 3 cells which are =$I$1&$E2&$F2
$I$1 =TODAY() $E2 is a Text Value and $F2 is a text value

B6 contains a date $D$1 abd $E$1 contain text values

What happens is the countif counts occurances where Date and both text values are the same and puts the count in a cell.
I want this value to stay the same when the TODAY() value changes

Share

    1 Answer

    1. Best Answer
      You can’t “turn off” the update by setting anything equal to “False”.

      What you need to do is either:

      1 – Use VBA to replace the formula with the current value, thus losing the formula. In it’s simplest form it would look like this:

      Sub MakeStatic()
 Range("A1") = Range("A1").Value
End Sub

      Based on your example, something like this should work:

      Sub MakeStaticRng()
 For rw = 3 To 3000
   If Range("B" & rw) < Range("F1") Then
      Range("C" & rw) = Range("C" & rw).Value
   End If
 Next
End Sub

      Note: I suggest you test this in a backup copy of your workbook, since you are going to lose the formulas and Macros cannot be undone.

      If you don’t want to lose the formula, then you would need VBA to put the formula back in the cell based on some other criteria.

      Bottom line is that you can’t have a formula that’s dependent on a volatile function like TODAY() and a static value in a cell at the same time.

      If you use this code in a Worksheet_Change event, it will fire automatically when your worksheet changes. You can control when it does what it is does by having the code test the address of the changed cell so that it doesn’t fire unnecessarily. If that is of interest to you, you’ll need to tell us what change(s) should trigger the code.

      Or…

      2 – Eliminate the formula completely and use VBA to do the entire calculation and place the results in the cells. That’s a bit more complicated, but it could be done.

      • 0