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

you would need to use intentional circular references.


go to Stephen Bullen's site

http://www.oaltd.co.uk/Excel/Default.htm

and look at "LastChng.zip"

This isn't exactly what you want, but the technique is the same.

You would name your cells (insert=Name=Define) and in the formula refer to
the name which will be adjusted if the user inserts or deletes rows.

--
Regards,
Tom Ogilvy


wrote in message
ups.com...
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

Got it. Thanks. I was trying so hard not to use a circular reference
that I didn't realize it is exactly the right solution to this problem.

Name the cell "RHigh" and then use the calculate event, below. Copy the code, right-click the sheet
tab, select "View Code", and paste the code in the window that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Calculate()
If Range("RHigh").Value Range("RHigh")(1, 2).Value Then
Application.EnableEvents = False
Range("RHigh")(1, 2).Value = Range("RHigh").Value
Application.EnableEvents = True
End If
End Sub

wrote in message ups.com...
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