Need help to freeze or override a formula

[Guest]

I need help to freeze a formula after the formula has picked the correct
date. I have given an example of my spreadsheet and a copy of the formula
that I use to get the right date. Is there anything function that I can add
to override of freeze the formula.

Page 1
A B C E F G
symbol high close date Profit Profit Date
abc 50.55 50.34 29/12/06 $50.86
hij 61.94 61.4 29/12/06 $62.03
let 54.44 53.66 29/12/06 $54.22

This is the formula that is in column "G
=IF(LOOKUP($A6,Page2!$A$2age2!$A$38,page2!$B$2age2!$B$38)>F6,LOOKUP($A6,page2!$A$1age2!$A$40,page2!$D$1age2!$D$40),"")
Page2
A B C D
symbol high close date
abc 46.85 46.78 09/02/07
let 42.89 41.7 09/02/07
hij 62 60.62 09/02/07

Page 2 critera I update everyday. So when the two symbols match in column A
on page 1 and 2 and the the close price on page 2 (column "c") is greater the
the profit price on page 1 (column "f") the date on page 2 (column "d") is
enterd into column "f" on page 1. So my question is when column "f" on page
one has a date enterd into it, is there a way to override the formula so it
won't pick up a new date the next time I update the information in page 2?

Thanks

 

[Earl Kiosterud]

John,

I didn't delve into your formula, but here's a general solution, which you may be able to
use.

=IF(C4="",0,IF(MAX(A3:A6)>C3,MAX(A3:A6),C3))

This formula goes in C3. Iteration has to be allowed (Tools - Options - Calculation). Set
maximum iterations to 1.

It picks the highest value in A3:A6 and freezes it there until a higher one comes along,
which it then grabs and freezes. C4 has anything non-blank in it. To reset the value, make
C4 blank. You can substitute your expression for MAX(A3:A6).