I have a spreadsheet that lives forever, constantly being saved and
resaved either with the same name or a new name. That spreadsheet
contains several cells whose values change regularly, during the
dynamic life of the spreadsheet. Consider for instance cell C5, which
has a formula that currently evaluates to 14. In another cell, call it
D5, I want to record the greatest value that C5 has ever been; for
instance, if the value in C5 changes to 17, D5 should also contain 17,
but if the value in C5 subsequently changes to 12, the value in D5
should remain 17. The "record high" must be preserved across saves of
the spreadsheet; if the spreadsheet is saved and closed, then reopened,
the value in D5 should still be 17. Further, the record high must be
preserved when the spreadsheet is restructured; for instance, if a row
is insert above row 5, so that C5 moves to C6 and D5 moves to D6, the
value in D6 must be the record high over the life of the spreadsheet
that has ever appeared in C6.
The obvious way to make this work is to write a function
RecordHigh(Cell) that saves the record high in some hidden worksheet
that is saved with the spreadsheet. Then, the formula in cell D5 would
be =RecordHigh(C5). But I haven't been able to make this work. Does
anybody have any ideas how to do this?
Phil
Bookmarks