You can use a circular reference to do this. On the Tools menu, choose
Options, then the Calculation tab. There, check the "Iterations" box.
Then, use the following formula

=MAX(A1,B1)

B1 will be a perisistent maximum of A1. If A1 exceeds B1, B1 will get
this new value. If A1 is less than B1, B1 remains the same. In other
words, B1 will be the maximum value ever reached by A1, regardless of
the current value of A1.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Mon, 27 Apr 2009 05:14:01 -0700, torwalker
wrote:

Hi I want to use a formula or similar to update a cell when another cell has
a higher value, and maintain this relationship, e.g. cell B1 will always
contain the maximum value held by cell A1 over time where A1 is a calculated
value. So I want to paste the value from A1 into B1 when A1 exceeds B1 but
not otherwise and as there is a whole column I would like this to be
automatic.
Sorry but I can't for the life of me work out how to do it and haven't found
a forum topic that does what I want. Any help greatly appreciated.
Regards