Data Validation

[mbailey]

Hello,

I can't quite figure this one out!

In cell a1 I want to be able to enter a price.
in cell a2 I have another price.
in cell a3 I have the ratio of a1/a2
I want a data valuation error alert to appear in a1 if a3 is greater than 40%.

At the moment my data valuation error only appears if I manually change a3 to greater than 40%.

Thanks for your help

 

[Tigs81]

mbailey

You should be able to use the formula section for the custom option, in the cell A1 data validation window

=IF(A3>0.4,FALSE,TRUE)

Thanks
tigs

 

[mbailey]

That's perfect - thanks very much

 

[mbailey]

Is there any way I can use the formula above in conjunction with a list of allowed values too?

Thanks

 

[Tigs81]

are you talking about a dropdown list?

or are you talking about just where you list the
=IF(OR("Red","Blue","ETC"),TRUE,FALSE)

or
=IF(AND(A3>0,A3<0.4),TRUE,FALSE)

Thanks
tigs

 

[Peter_SSs]

What about just having your list of allowed values in your A1 Data Validation and changing your A3 cell formula to something like this

=IF(A1/A2>0.4,"ERROR",A1/A2)