How do I avoid what I think is a circular reference?

[Richard in Redditch]

Hello everyone.
I have a requirement which I can't see my way through.
In cell A1, there is a monetary (and manually adjusted) amount, and I want to show in cell B1 the amount by which the new amount entered in A1 differs from the previous amount in A1. I know that I can use (and then hide) cells in C1 ... , but a reference back to the old A1 value (stored in C1) but this would lead to a zero result, as the new A1 value is automatically updated in C1!
Can anyone help, please?
Thank you.

 

[Caleeco]

The only way I have found to do what you need is with VBA.

Setup a Worksheet_Change Event that monitors cell A1.
Every time cell A1 changes, store the existing value in cell C1 (static value).
That way you dont end up with a circular ref

Regards
Caleeco

 

[Richard in Redditch]

The only way I have found to do what you need is with VBA.

Setup a Worksheet_Change Event that monitors cell A1.
Every time cell A1 changes, store the existing value in cell C1 (static value).
That way you dont end up with a circular ref

Regards
Caleeco

Many thanks for your prompt response and very helpful idea. I will give it a go!
Richard