Back in my newsgroup days, about once a month someone would ask how to have a formula that accumulated entries from another cell. For example, put a formula in C3 that refers to A3. Every time a value is entered in A3, have the value in C3 change by that amount. Impossible right?
The standard answer is that you can’t and that you should use a Worksheet_Change event to monitor A3. When it changes, change the value in C3. Then I read Charles Williams’ comments about the Text property
When used inside a UDF you can use .Text to get the formatted value of the calling cell as it was before the UDF was called (you can’t do this with .Value or .Value 2 without causing a circular reference).
I had no idea that was true. Now I can write a UDF like this:
Function UpDown(dChange As Double) As Double
Dim sOld As String
sOld = Application.Caller.Text
UpDown = Val(sOld) + dChange
In C3, I put
=updown(A3). Whenever I change A3, C3 changes by that amount. I’m sure there are all kinds of problems with this – dates and errors to name two – but I thought it was interesting nonetheless. Apologies if the entire Excel universe knows this already, but it’s new to me.