Tell Cell to Retain Value

[ffffloyd]

I would like to figure out a way of telling a cell not to change its value if a certain condition exists; basically to save time when I have a sheetload of volatile functions or when I populate a data sheet from values in a separate workbook iff it is open.

I've thought about a function that returns the cell's own value that I could call if I didn't want the value to change, but of course I get the old circular reference problem. Is there a way of programmatically switching off the circular reference in cases where I want to allow it? But then, even if I did that, would it force a zero into the cell?

Or has anyone else managed to deal with the problem of not changing a cell's value? Is there a totally different way of going about it?

[Mordred]

Hi fffffloyd, I don't think this would be a problem, with a written procedure the cell's value can be stored as a variable and then placed back into the cell at the end of the procedure. However, your cell area is no doubt specific and knowing what functions will be affecting the cell would be only guess work. To help you, you need to help us by uploading a mock workbook with non-sensitive data in it.

[ffffloyd]

Thanks, Michael, that would seem like a sensible way to go but I have tried it and it doesn't work.

You'd think by assigning the cell value to a local variable and then assigning that local variable to the function result, it would dissociate the source of the value from the cell itself, but it still comes up with a circular reference. I have tried both of these functions below, and have tried using both Value and Value2 in the second one, to no avail:

Please Login or Register  to view this content.

There's no need for me to mock up a test sheet and for you to have to download it. Just open a new sheet, put 1 in cell A1 and =IF(ISNUMBER(A1),A1,RetainValue(A2)) in cell A2. As long as you keep a number in A1, it will replicate that in A2. But then change A1 to a letter and see if you can trick A2 into retaining the last number.

[Mordred]

Well this tricks it into keeping the previous number but it doesn't turn off the circular reference notification. I even tried setting Display Alerts to False with no change. Anyways, I changed the variable from a Variant to a String and it will retain the value.

Please Login or Register  to view this content.

[ffffloyd]

Yeah, thanks Michael, it does retain the previous value but what I really want to do is turn off that annoying Circular Reference Warning as well. There must be a way!

[Mordred]

For some reason, the following works without circular warnings (except for when I originally entered the formula:

Please Login or Register  to view this content.

[ffffloyd]

Hmmm, it is not suppressing the alert for me at all; and that would make sense: the DisplayAlerts property is only applicable while the code is running. It is the worksheet that realises the circular reference has occurred. Even if I set DisplayAlerts to false and leave it off, it still comes up with the warning.

If I set the number of iterations to 1 though (via Tools | Options | Calculation) then the message does not appear. Now though, I am worried about what else I have switched off by reducing the number of iterations! I seem to remember that reducing the number of iterations was the solution to something else in the past, though my ageing brain won't allow me to remember what it was.

What is this "number of iterations" referring to? I would have thought that was for "what if" scenarios, but I don't use them.

[ffffloyd]

But then again, no. Resolving circular references is precisely what the "number of iterations" option is for, viz:

Enable iterative calculation When selected, this option allows iterative formulas (also known as circular references) to be calculated. Unless you specify otherwise, Excel stops after 100 iterations or when all values change by less than 0.001. You can change this iteration limit by typing the number that you want in the Maximum Iterations box, the Maximum Change box, or both boxes. When the Enable iterative calculation check box is cleared, circular references cannot be calculated, and an error message is displayed.

[ffffloyd]

I don't even need a fancy function if that's the case. I can just put the cell reference directly there and it works (provided I don't subsequently try and edit the cell formula).

But I'd still like to know why, and how, assigning a cell value to an unrelated variable and then assigning it back alerts Excel to the fact that there is a circular reference. Just so I know, because I'm a curious fellow.

[mikerickson]

You could use this UDF.
If you put =FreezeValue(C1=1, A1+A2) in a cell. It will show the sum of A1 and A2, if C1<>1. If C1=1, it will return the value of A1+A2 at the time that C1 became 1.
If freeze_condition is True, the value of the function will not change.
If freeze_condition is False, the value of the function is the value of unfrozen_value.

Please Login or Register  to view this content.

Note that =FreezeValue(A1=1, RAND()) will generate a random number that changes at the user's command.

[DonkeyOte]

If the formula/function in A2 refers to itself by virtue of range precedents it will be circular by nature.

[mikerickson]

Have you looked at Calculation - Automatic Except for Data Tables?

[ffffloyd]

Mike, thanks; your FreezeValue function works whether iterative calculation is on or not.

Mike wins!