Time Stamp/Locking Cell Computation

[Guest]

Hi:
I have a worksheet with a row that shows half hour intervals (7AM, 7:30AM,
8AM, etc) all day long and in the rows below, I have financial data from
outside sources constantly updating.
I want the cells in the each column to compute their formulas at each time
of day and then lock or freeze, so that they are unchanged for the day.
So, for example, at 7AM, cell A2 calculates and then locks and then at 7:30,
Cell B2 computes and freezes, etc. So the data streams continuously in the
cells to the right and then a certain time occurs and it freezes.
Any ideas/Suggestions?
Thank you sooooooooooo much-

 

[Tom Ogilvy]

assuming DDE updates and xl2000 or later, use the change event to replace
the formula with the displayed value.

 

[gifer]

Patrick,

I do the same thing in 5min intervals... and I use a scheme where I compare
the current time against a stated time in a cell. If the static cell value
is 7:00AM, and the current time on the PC is 7:05AM, then a formula in
another cell does not write over its self. If the static cell value 7:00AM
is equal or greater than the time on the PC, then the formulas in another
cell equals the value in your "outside data" cell. In English that is, if
time is less than the PC time, then freeze the cell. For my sheet/idea...

In cell C1 =MOD(NOW(),1) This takes the PC's clock time/date and extracts
the time portion only. So, C1 = the time of day.

A2 is 7AM, B2 is 7:30AM and so on.

A1 holds the external data value. This value will be overwritten every time
it updates from outside, using "Get External Data" refreshing every 5 min.
which also causes all my sheets to "calculate" or update; which is critical
to the whole idea.

In cell A3 this formula: =IF(A2<=$C$1,A3,$A$1) This causes the cells in the
past, to equal its self or freeze, and everything in the future to equal the
outside value cell. For Excel to deal with this formula which is a circular
reference, you need to go to Tools/options/ calculation tab, and check
Iterations.

This may not be the best way, and it poses a few problems in different
circumstances, but it does what I need which is drawing continuous updating
historical line charts. I will try the change event method that Tom
suggested and see if it does the same thing or works better.

John