Keeping values in place when formulas change

[Gert Renkin]

Hi everyone, I have the following question.

And already my apologies but I can't post a picture or a mini-sheet due to company-policy.

Say I have a value in Cell H2 (50), which the result is of a formula/calculation, which is always changeable.

When Cell D2 (changeable value = Queen) matches with Cell B8 (fixed value = Queen) the result of Cell H2 (50) should appear in Cell C8 (FORMULA=IF(D2=B8,H2))

When Cell D2 (changeable value = Prince) matches with Cell B9 fixed value = (Prince) the result of Cell H2 (40) ( which changed due to the formula) should appear in Cell C9 (FORMULA=IF(D2=B9,H2))

The only problem is that when I do the match of Cell D2 with Cell B9 the value in Cell C8 disappears because the link between D2 & B8 was removed and Cell C9 contains the value of 40

How can I see to it that the value of 50 (H2) that was entered in Cell C8 (which is the result of a changeable formula) does not get lost.

 

[6StringJazzer]

Let me make sure this is clear.

1. D2 is given the value of "Queen". Now the result of the formula in H2 becomes 50. The result of the formula in C8 is the H2 result of 50. (Based on the formula you showed, C9 should be FALSE at this point.)
2. D2 is given the value of "Prince". Now the result of the formula in H2 becomes 40. The result of the formula in C9 is the H2 result of 40. (Based on the formula you showed, C8 now shows FALSE.)

You are saying that after step 2, you do not want C8 to revert to FALSE. Once the formula in C8 has a result of 50, you want it to stay there forever, no matter what else changes.

Am I understanding the question correctly?

If so, based on the formulas you showed, you will need a VBA solution. Given that your company is pretty restrictive, is that even allowed?

 

[Gert Renkin]

Maybe I should explain better.
Point 1 in your reply = D2 is a cel where I can put any information in. Once this matches with cell B8 (which is a fixed value) I want the value of 50 (Cell H2) to copy in Cell C8.
The value 50 is created totally separate of all other cells using a specific but separate formula.
Point 2 in you reply = the same way of thinking.
So when I do Point 1 the value of 50 is copied into Cell C8, because there is a match between D2 and B8.
So when I do Point 2 the value of 40 is copied into Cell C8, because there is a match between D2 and B9.
But before this happens the value in H2 changed due to a formula.
The problem is that I have to use the same formula in Cell C8 & Cell C9 which pulls information out of H2.
As of the moment that I use the formula in Cell C9 the other cell Cell C8 becomes false because the link between D2 and B8 is gone.

 

[6StringJazzer]

So when I do Point 2 the value of 40 is copied into Cell C8

I think you mean C9 here.

I think your explanation is consistent with what I wrote.

Basically you are saying you have a formula in C8, and once the formula returns a number, you want that number to "stick." You don't want the formula to recalculate if the precedents change.

If that's the case, as I said, you need VBA. Can you use VBA in your environment?

(It might be possible with a deliberate circular reference but I would have to test it.)

 

[Gert Renkin]

I think you mean C9 here.

I think your explanation is consistent with what I wrote.

Basically you are saying you have a formula in C8, and once the formula returns a number, you want that number to "stick." You don't want the formula to recalculate if the precedents change.

If that's the case, as I said, you need VBA. Can you use VBA in your environment?

(It might be possible with a deliberate circular reference but I would have to test it.)

Hi, thanks for replying and looking at this. Yes there is no problem to use VBA.

 

[6StringJazzer]

Try this in the module for the worksheet containing the data. If the formula in C8 returns a value other than FALSE, then that cell will be converted to the value. It will no longer have a formula and cannot change again. That is my understanding of what you want.

Private Sub Worksheet_Calculate()

   If [C8] <> False Then
      [C8].Value = [C8].Value
   End If

End Sub