Losing Data From Formulas When Cell Variable Changes

[Sam Spade]

I’m hoping that you might be able to shed some light on an issue tha
I’m having with the MS EXCEL functions. I’m simply trying to retai
Cell Values produced from formulas that are activated by Time. Thi
will allow an immediate visual comparison from one time interval to th
next as well have data to be stored later.

Let’s say I have a single cell (D5) that automatically changes th
numerical value with time (say minutes). This is the triggerin
mechanism. When the cell D5 reads 30 (minutes) a series of cells wit
formulas are activated and produce cell values.
For Cell F5 ( =IF (D5=30, formula A)
For Cell F6 ( =IF (D5=30, formula B)
For Cell F7 ( =IF (D5=30, formula C)
Etc., etc.....

This all works very nicely (for a minute).
However, as soon as the timing cell changes numerically to 29 (minutes)
all of the formula Value cells lose the data created at the 30 min
interval. Then when the D5 cell reads 25 (minutes) the cells wit
formulas are once again activated and produce cell values. Then at 2
(minutes) the data is lost, and so on, and so on......

I would like to automatically retain all of the cell values that wer
produced at each designated time interval even if it means repeating th
series of Value cells for each time interval.
Cells F5 thru F15 for 30 minute interval
Cells G5 thru G15 for 25 minute interval
Cells H5 thru H15 for 20 minute interval
Etc., etc.....
Is there any type of function that can be included to lock the data i
once it’s produced? I've also tried the COUNTIF function to check th
timing cell, but that also reverts to "0" when this cell changes

 

[GS]

It happens that Sam Spade formulated :

I’m hoping that you might be able to shed some light on an issue that
I’m having with the MS EXCEL functions. I’m simply trying to retain
Cell Values produced from formulas that are activated by Time. This
will allow an immediate visual comparison from one time interval to the
next as well have data to be stored later.

Let’s say I have a single cell (D5) that automatically changes the
numerical value with time (say minutes). This is the triggering
mechanism. When the cell D5 reads 30 (minutes) a series of cells with
formulas are activated and produce cell values.
For Cell F5 ( =IF (D5=30, formula A)
For Cell F6 ( =IF (D5=30, formula B)
For Cell F7 ( =IF (D5=30, formula C)
Etc., etc.....

This all works very nicely (for a minute).
However, as soon as the timing cell changes numerically to 29 (minutes),
all of the formula Value cells lose the data created at the 30 min.
interval. Then when the D5 cell reads 25 (minutes) the cells with
formulas are once again activated and produce cell values. Then at 24
(minutes) the data is lost, and so on, and so on......

I would like to automatically retain all of the cell values that were
produced at each designated time interval even if it means repeating the
series of Value cells for each time interval.
Cells F5 thru F15 for 30 minute interval
Cells G5 thru G15 for 25 minute interval
Cells H5 thru H15 for 20 minute interval
Etc., etc.....
Is there any type of function that can be included to lock the data in
once it’s produced? I've also tried the COUNTIF function to check the
timing cell, but that also reverts to "0" when this cell changes.

The dependent cells change their value to the 'False' condition of your
IF formulas. You could do this via VBA macros to put constant values
(not formulas) in those cells if that's an option.

 

[Sam Spade]

;1281399']It happens that Sam Spade formulated :-

I'm hoping that you might be able to shed some light on an issue that
I'm having with the MS EXCEL functions. I'm simply trying to retain
Cell Values produced from formulas that are activated by Time. This
will allow an immediate visual comparison from one time interval t the
next as well have data to be stored later.

Let's say I have a single cell (D5) that automatically changes the
numerical value with time (say minutes). This is the triggering
mechanism. When the cell D5 reads 30 (minutes) a series of cell with
formulas are activated and produce cell values.
For Cell F5 ( =IF (D5=30, formula A)
For Cell F6 ( =IF (D5=30, formula B)
For Cell F7 ( =IF (D5=30, formula C)
Etc., etc.....

This all works very nicely (for a minute).
However, as soon as the timing cell changes numerically to 2 (minutes),
all of the formula Value cells lose the data created at the 30 min.
interval. Then when the D5 cell reads 25 (minutes) the cells with
formulas are once again activated and produce cell values. Then a 24
(minutes) the data is lost, and so on, and so on......

I would like to automatically retain all of the cell values that were
produced at each designated time interval even if it means repeatin the
series of Value cells for each time interval.
Cells F5 thru F15 for 30 minute interval
Cells G5 thru G15 for 25 minute interval
Cells H5 thru H15 for 20 minute interval
Etc., etc.....
Is there any type of function that can be included to lock the dat in
once it's produced? I've also tried the COUNTIF function to chec the
timing cell, but that also reverts to "0" when this cell changes.-

The dependent cells change their value to the 'False' condition of you

IF formulas. You could do this via VBA macros to put constant values
(not formulas) in those cells if that's an option.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I believe you are correct. As soon as the “False” condition exists th
values arrived at through the formulas in each cell is lost. I’ve neve
done a VBA macro before so bare with me. When you mentioned “constan
values”, could these values be derived by the same math used in th
existing cell formulas?
As a simple example, lets say I’m using the following formula in cel
F5:
( =IF (D5=30, (M5/(M4+M3))

So when D5 is “True” at the 30 minute interval whatever the values ar
at that time in cells M3, M4, and M5 will enter the formula and provid
a new value result in cell F5 (that I want to keep). As each new tim
interval arrives the values in the “M” will also change and produc
another new value in F5.

My thinking was to dedicate a series of cells for each time interval s
that the result from each interval is retained and wouldn’t get lost o
overwritten by each succeeding interval.

Could you inform me as to how a VBA macro would handle something lik
this?
Thank

 

[GS]

Sam,
I'm having a rather busy day but if you post back an example of the
formulas you want to use for F5:F15 that would be helpul. Better yet if
you can attach your file or a link to where I can download it.

 

[GS]

GS wrote on 2/1/2012 :

Sam,
I'm having a rather busy day but if you post back an example of the formulas
you want to use for F5:F15 that would be helpul. Better yet if you can attach
your file or a link to where I can download it.

I see that this is duplicate in cols G:H and so I'll need those
formulas as well.

 

[Sam Spade]

;1291952']GS wrote on 2/1/2012 :-

Sam,
I'm having a rather busy day but if you post back an example of th formulas
you want to use for F5:F15 that would be helpul. Better yet if you ca attach
your file or a link to where I can download it.-

I see that this is duplicate in cols G:H and so I'll need those
formulas as well.

--
Garry

Free usenet access at http://www.eternal-september.org

ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi Garry
I really appreciate your offer, but with your initial advice I ha
already gotten one of my friends involved who is also very familiar wit
VB. He’s sending me the VB code that I’ll need to create those custo
functions.
Thanks again

 

[GS]

Sam Spade expressed precisely :

;1291952']GS wrote on 2/1/2012 :-

Sam,
I'm having a rather busy day but if you post back an example of the
formulas you want to use for F5:F15 that would be helpul. Better yet if
you can attach your file or a link to where I can download it.-

I see that this is duplicate in cols G:H and so I'll need those
formulas as well.

--
Garry

Free usenet access at http://www.eternal-september.org

ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi Garry
I really appreciate your offer, but with your initial advice I had
already gotten one of my friends involved who is also very familiar with
VB. He’s sending me the VB code that I’ll need to create those custom
functions.
Thanks again!

Sam,
No problem! Let me know how you made out...