Hi,

I've recently started writing functions in Excel which I then call from
cells to perform calculations etc. However, I am aware that a function
cannot alter the value of *another* cell. What I want to do is this :

<Cell 1 is usually empty.
When <Cell 1 changes from empty to any other value, take a snapshot of
<Cell 2 (which updates constantly) and copy it into <Cell 3.
<Cell 2 will then continue to update as usual, whilst <Cell 3 will contain
a static value.
Then when <Cell 1 becomes empty again, erase <Cell 3 and that's it.
Then keep repeating this whole process as long as the spreadsheet is open.

The concept sounds simple enough and I'm sure it must be possible but I
can't for the life of me figure out how! All I've been using so far is
functions, which take arguments and return values. Any help appreciated!

Many thanks,

Tom