Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Stamp/Locking Cell Computation
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 "Patrick123456789" wrote in message ... 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- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i set up a time stamp if another number entered in cell? | Excel Discussion (Misc queries) | |||
is it possible to time stamp cell comments? | Excel Discussion (Misc queries) | |||
is it possible to time stamp cell comments? | Excel Discussion (Misc queries) | |||
Inserting time stamp onchange of any cell | Excel Programming | |||
Setting up a date and time stamp in a cell | Excel Programming |