Slight problem with data validation in Excel 365 In insider

[TheOldPuterMan]

Simple problem. I have two values that I want to make sure match. First is Budgeted_Amount "Q3" and secondly is  Actual_Amount "S3".

I want to set up Data Validation to compare these cells but I either get an error on my formula while setting up validation or no action at all.

"=IF(Q3<>S3,1,0)" produces an error in data validation. "=@INDIRECT(Q3<>S3)" does not produce an error during setup bus doesn't work.

I know that it's a very simple thing but....

Thanks

TheOldPuterMan AKA John

[#2]

Hi John,

Do you mean that the Q3 and S3 should always be equal? How Q3 and S3 get populated? Do they contain manual entries?

Please note that there is a flaw in the custom validation i.e. if you copy any value and paste it over the cell with custom validation, it will overwrite the validation rule and the custom validation will stop working.

[#3]

@Subodh_Tiwari_sktneer 

Thank you for responding.

Q and S are both manually entered currency amounts that can be zero (Empty), contain a number or not. They are equal most of the time but do not have to be. All I want to do is flag a "Possible" data entry error when there is a value entered in "S" that does not match the value in "Q".

I am aware of the Copy/Paste issue, thank you.

TheOldPuterMan AKA John

[#4]

In that case, select S3 and apply the Custom Data Validation using the formula give below...

=S3=Q3

That will give an error if the value entered in S3 is not equal to the value in Q3.

And if you enter the value in S3 first and then in Q3 and want to make sure that the value entered in Q3 must be equal to the value exists in S3, apply a Custom Data Validation in Q3 using the formula...

=Q3=S3

If that takes care of your original question, please accept this post as an Accepted/Best response which will mark your question as Solved.