Page 1 of 1

Inactivate/ignore cells with a formulas after first use

PostPosted: Wed Dec 19, 2007 11:29 pm
by danr
Does anyone know how I can inactivate/ignore cells that contain a formula ater first use?
I have a spreadsheet that uses a formula with a time stamp =NOW()..., in it and it is basically repeated on down the column from B3-B2000, when certain conditions are met, through out the given time span (B3-B2000).
I need to keep the time stamp for each cell, after it has been activated once and then not have the cell accessed again when the next time frame comes along. Currently every cell is activated everytime - not satisfactory.
Does anyone have a solution for me?
Thanks for any help.

Re: Inactivate/ignore cells with a formulas after first use

PostPosted: Thu Dec 20, 2007 4:03 am
by TerryE
The basic issue that you have is the functional form of the calc engine. It goes to great lengths to prevent procedural dependencies so that row or column order make no difference. The thing that is important is the dependencies between cells generated by the formulae. It is not statful.

The sort of thing that you might think would work is
    B1: =IF(AND(NOT(ISBLANK(A1);ISBLANK(B1));=NOW();B1)
However, this is a circular dependency on B1 and the engine doesn't know how to evaluate these so generates an error. Doing this is complicated. You would probably need to resort to some Basic scripting. Sorry