Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Losing Data From Formulas When Cell Variable Changes
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Losing Data From Formulas When Cell Variable Changes
It happens that Sam Spade formulated :
Im hoping that you might be able to shed some light on an issue that Im having with the MS EXCEL functions. Im 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. Lets 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 its 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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
|
|||
|
|||
Quote:
As a simple example, lets say I’m using the following formula in cell F5: ( =IF (D5=30, (M5/(M4+M3)) So when D5 is “True” at the 30 minute interval whatever the values are at that time in cells M3, M4, and M5 will enter the formula and provide a new value result in cell F5 (that I want to keep). As each new time interval arrives the values in the “M” will also change and produce another new value in F5. My thinking was to dedicate a series of cells for each time interval so that the result from each interval is retained and wouldn’t get lost or overwritten by each succeeding interval. Could you inform me as to how a VBA macro would handle something like this? Thanks |
#4
|
|||
|
|||
Quote:
I just read through an interesting VBA tutorial on creating and using Custom Functions. If I’m not mistaken it looks like I might be able create the “constant value” you mentioned as each Time Interval arrives. This constant value would then be inserted into a designated cell. This way each of the existing dependent Formula cells (F5, F6, F7, etc.) would refer to that cell value instead of continually changing Time cell (D5). As long as the value remains constant I should never lose the data created. I guess I'll also have to incorporate a way to reset the constant values (to maybe "0") after an entire Timing cycle is completed. Does that sound about right? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Losing Data From Formulas When Cell Variable Changes
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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Losing Data From Formulas When Cell Variable Changes
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 |
#7
|
|||
|
|||
Quote:
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! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Losing Data From Formulas When Cell Variable Changes
Sam Spade expressed precisely :
'GS[_2_ Wrote: ;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. Hes sending me the VB code that Ill need to create those custom functions. Thanks again! Sam, No problem! Let me know how you made out... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COPY WORKBOOK KEEPING FORMULAS AND TEMPLATES, BUT LOSING THE DATA | Excel Discussion (Misc queries) | |||
How to insert a row without losing data in INDEX formulas | Excel Worksheet Functions | |||
entering new data in a saved spreadsheet without losing formulas? | New Users to Excel | |||
How do I clear data values wthout losing the formulas? | Excel Worksheet Functions | |||
Variable losing its value when calling new sub | Excel Programming |