Home

Slight problem with data validation in Excel 365 In insider

%3CLINGO-SUB%20id%3D%22lingo-sub-855729%22%20slang%3D%22en-US%22%3ESlight%20problem%20with%20data%20validation%20in%20Excel%20365%20In%20insider%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-855729%22%20slang%3D%22en-US%22%3E%3CP%3ESimple%20problem.%20I%20have%20two%20values%20that%20I%20want%20to%20make%20sure%20match.%20First%20is%20Budgeted_Amount%20%22Q3%22%20and%20secondly%20is%26nbsp%3B%20Actual_Amount%20%22S3%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20set%20up%20Data%20Validation%20to%20compare%20these%20cells%20but%20I%20either%20get%20an%20error%20on%20my%20formula%20while%20setting%20up%20validation%20or%20no%20action%20at%20all.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22%3DIF(Q3%26lt%3B%26gt%3BS3%2C1%2C0)%22%20produces%20an%20error%20in%20data%20validation.%20%22%3D%40INDIRECT(Q3%26lt%3B%26gt%3BS3)%22%20does%20not%20produce%20an%20error%20during%20setup%20bus%20doesn't%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20that%20it's%20a%20very%20simple%20thing%20but....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETheOldPuterMan%20AKA%20John%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-855729%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-855746%22%20slang%3D%22en-US%22%3ERe%3A%20Slight%20problem%20with%20data%20validation%20in%20Excel%20365%20In%20insider%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-855746%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20John%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20mean%20that%20the%20Q3%20and%20S3%20should%20always%20be%20equal%3F%20How%20Q3%20and%20S3%20get%20populated%3F%20Do%20they%20contain%20manual%20entries%3F%3C%2FP%3E%3CP%3EPlease%20note%20that%20there%20is%20a%20flaw%20in%20the%20custom%20validation%20i.e.%20if%20you%20copy%20any%20value%20and%20paste%20it%20over%20the%20cell%20with%20custom%20validation%2C%20it%20will%20overwrite%20the%20validation%20rule%20and%20the%20custom%20validation%20will%20stop%20working.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-855832%22%20slang%3D%22en-US%22%3ERe%3A%20Slight%20problem%20with%20data%20validation%20in%20Excel%20365%20In%20insider%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-855832%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20responding.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQ%20and%20S%20are%20both%20manually%20entered%20currency%20amounts%20that%20can%20be%20zero%20(Empty)%2C%20contain%20a%20number%20or%20not.%20They%20are%20equal%20most%20of%20the%20time%20but%20do%20not%20have%20to%20be.%20All%20I%20want%20to%20do%20is%20flag%20a%20%22Possible%22%20data%20entry%20error%20when%20there%20is%20a%20value%20entered%20in%20%22S%22%20that%20does%20not%20match%20the%20value%20in%20%22Q%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20aware%20of%20the%20Copy%2FPaste%20issue%2C%20thank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETheOldPuterMan%20AKA%20John%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-855902%22%20slang%3D%22en-US%22%3ERe%3A%20Slight%20problem%20with%20data%20validation%20in%20Excel%20365%20In%20insider%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-855902%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20that%20case%2C%20select%20S3%20and%20apply%20the%20Custom%20Data%20Validation%20using%20the%20formula%20give%20below...%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DS3%3DQ3%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThat%20will%20give%20an%20error%20if%20the%20value%20entered%20in%20S3%20is%20not%20equal%20to%20the%20value%20in%20Q3.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20if%20you%20enter%20the%20value%20in%20S3%20first%20and%20then%20in%20Q3%20and%20want%20to%20make%20sure%20that%20the%20value%20entered%20in%20Q3%20must%20be%20equal%20to%20the%20value%20exists%20in%20S3%2C%20apply%20a%20Custom%20Data%20Validation%20in%20Q3%20using%20the%20formula...%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DQ3%3DS3%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20that%20takes%20care%20of%20your%20original%20question%2C%20please%20accept%20this%20post%20as%20an%20Accepted%2FBest%20response%20which%20will%20mark%20your%20question%20as%20Solved.%3C%2FP%3E%3C%2FLINGO-BODY%3E
TheOldPuterMan
Occasional Contributor

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

3 Replies

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.

@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

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies