lock Formula value
Posted on 1999-07-22
one problem I always seem to have with my spreadsheets is that when a particular formula is true I want that value to remain as it is regardless if the cell value it is referencing changes. an example of this is in cell a2 the formula would read =IF(a1=5,NOW(),"") works fine when a1 = 5 but every time excel calculates the time changes. or if the value of a1 changes it ="" . The only way Ive been able to overcome this is to use a circular reference eg in cell a2 the formula reads =IF(b1<>"",a2,IF(a1=5,NOW(),""))
Not the best of solutions, is there another way?