Preserving Previous Formula Values

[Phil Hageman]

The "X" cell formulas below return a value based on
entries in cells U19 through U30 (12 months of a year).
The values for V10 and P10 may also change each month.
This system works fine for the immediate month being
posted; however, it returns a false history for previous
months, due to the changes in V10 and P10. I have to
maintain a history of the values returned in the "X"
cells - that is, once a value is entered in, say, U20, the
value in X19 (the previous month) is preserved -
regardless of new values entered in V10 and P10. Also, if
the user deletes an entry for a current month, the formula
works as before - as though no entry was ever made - kind
of a built-in undo.

There are 48 yearly ranges involved in this reporting
system, so I'm not sure if this is a cell formula or
module coding issue.

Is this possible? If so could someone help me through the
code?

Cell Formula
X19 =IF(U19="","",IF(U19<>0,(U19-V10)/(P10-V10),0))
X20 =IF(U20="","",IF(U20<>0,(U20-V10)/(P10-V10),0))
X21 =IF(U21="","",IF(U21<>0,(U21-V10)/(P10-V10),0))
Etc.
X30 =IF(U30="","",IF(U30<>0,(U30-V10)/(P10-V10),0))

 

[Patrick Molloy]

One method would be to use the Sheet's CHANGE event
taht's fired whenever a value is entered. The code below
should be pasted to the sheet's code page - right click
the sheet tab & goto View Code
The code is tests to see if the cell that has changed is
in the column U10:U30
If it is then the value is calculated an paced in cell X
of the same row/ So enter a value in U25 then X25 will
get the value from the formula.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rw As Long
rw = Target.Row
' test target column
If Target.Column = 21 Then '"h"
' test if its a 'month'
If rw >= 19 And rw <= 30 Then
Cells(rw, "X").Value = _
(Target.Value - Range("V10").Value) / _
(Range("P10").Value - Range("V10").Value)
End If
End If
End Sub

NOTE The values in P10 and V10 will be used by the code,
so they must be entered BEFORE the value in U

Patrick Molloy
Microsoft Excel MVP

 

[Phil Hageman]

Patrick,

This is working. Thanks for your help. One thing I did to
help me understand what is happening, I added an "o"
in "rw" to make it "row" - did this everywhere "rw" was
coded. Now I need to refine the code some.

When I post a value in a "U" cell, a new "X" value is code
entered, and the previous "X" cell value remains (as it
should); however, if I delete the "U" posting, the value
in the "X" cell needs to be deleted too (the undo thing I
mentioned before) - by code.

As of now, when I delete the last posting (as if I were
correcting an incorrect entry), I get a Run-time
error '13': Type mismatch. In the code, and the three
following lines are yellow highlighted:
Cells(row, "X").Value = _
(Target.Value - Range("V10").Value / _
(Range("P10").Value - Range("V10").Value))

I need to modify the code to:
1. Delete the "X" cell code-provided entries
2. Resolve the Run-time error.

After 1. & 2. are resolved, the next phase is to expand
things. In this worksheet are 12 pairs of ranges working
together (like "U" and "X") needing inclusion into the
code:

U19:U30, X19:X30 (these are in the existing code)
AD19:AD30, AG19:AG30
AM19:AM30, AP19:AP30
AV19:AV30, AY19:AY30

U51:U62, X19:X30
AD51:AD62, AG62:AG62
AM51:AM62, AP62:AP62
AV51:AV62, AY62:AY62

U83:U94, X83:X94
AD83:AD94, AG83:AG94
AM83:AM94, AP83:AP94
AV83:AV94, AY83:AY94

Again, Patrick, thanks very much for your help.

Phil