No automatic validation check for formulas...

[0-0 Wai Wai ^-^]

If the validation cell contains value only, when we input a wrong value in the
cell, the validation checker will stop us from inputting.

However if it is a formula, eg:

A1 10
A2 8
A3 6
A4 sum(A1:A3) [value=24]

Validation: =A4=24

So afterward, if I change any value from A1 to A3 and make the value of A4
unequal to 24, eg:

A1 **9**
A2 8
A3 6
A4 sum(A1:A3) [value=23]

Validation: =A4=24

It won't stop/warn me in that way.
The only thing I can do is to check by myself using the formula auditing toolbar
and clicking the icon - "Circle Invalid Data".

Do you know if there is any workaround for this?

 

[Anders S]

Hi,

You can apply the same validation, =A4=24, on cells A1:A:3.

HTH
Anders Silven

 

[0-0 Wai Wai ^-^]

Hi, a very strange thing happened for your suggestion.

I can still change any figure in cell A3. But I can't change any in cell A1 & A2
(no matter the final outcome is equal to 24).

I'm afraid it doesn't work at all.

 

[Anders S]

Sorry, my mistake.

Validation should be =$A$4=24

Regards
Anders Silven

 

[0-0 Wai Wai ^-^]

Not really.
I forget to tell you I did copy as "=$A$4=24" because I don't think relative
reference do anything with my problem.

However it is still out of order in the manner I said previously.

Did you try it yourself in your computer?
If so and succeeded, would you mind to send the excel file to me, so I can have
a deep look at it.

My email: wai_wai_sir_plus[at]yahoo.com.hk

 

[Anders S]

The validation formula $A$4=24 works if applied to cells A1:A3. However, it is
pointless since it will not allow any changes to A1:A3 since any change will
yield a sum <>24 in cell A4.

Anders Silven