Home

Data validation using more than one formula for a single cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1067321%22%20slang%3D%22en-US%22%3EData%20validation%20using%20more%20than%20one%20formula%20for%20a%20single%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1067321%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20to%20find%20a%20way%20to%20use%20multiple%20Data%20Validations%20in%20a%20single%20cell.%20Currently%2C%20I'm%20simply%20validating%20newly%20entered%20data%20against%20the%20contents%20of%20another%20column%20on%20the%20same%20row%20to%20see%20if%20it's%20identical.%20If%20it%20is%20not%2C%20all%20is%20ok%2C%20if%20it%20is%20not%20I%20issue%20a%20warning%20message%20and%20wait%20for%20user%20response.%20The%20most%20basic%20kind%20of%20validation.%26nbsp%3B%3CSTRONG%3E(%3DS%3DQ)%3C%2FSTRONG%3E%20with%20%3CSTRONG%3E(%3C%2FSTRONG%3E%3CSTRONG%3ES)%3C%2FSTRONG%3E%20being%20a%20Budget%20Amount%20and%20(%3CSTRONG%3EQ)%3C%2FSTRONG%3E%20being%20the%20Actual%20Amount.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20expand%20that%20validation%20with%20a%20test%20to%20see%20if%20that%20value%20(%3CSTRONG%3ES)%26nbsp%3B%3C%2FSTRONG%3Eappears%20anywhere%20else%20on%20the%20sheet%20in%20conjunction%20with%20the%20same%20Provider%26nbsp%3B%3CSTRONG%3E(A)%3C%2FSTRONG%3E.%20Essentially%20if%20the%20Actual%20Amount%26nbsp%3B%3CSTRONG%3E(Q)%3C%2FSTRONG%3E%20is%20not%20equal%20to%20the%20Budgeted%20Amount%26nbsp%3B%3CSTRONG%3E(S)%3C%2FSTRONG%3E%26nbsp%3B%3CU%3Eor%3C%2FU%3E%20the%26nbsp%3Bcombination%20of%20(Provider%26nbsp%3B%3CSTRONG%3E(A)%3C%2FSTRONG%3E%20%3CU%3Eand%3C%2FU%3E%26nbsp%3BActual%20Amount%26nbsp%3B%3CSTRONG%3E(S)%3C%2FSTRONG%3E)already%20exists%20in%20the%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEssentially%20if%20%3CSTRONG%3E(Q%3DS)%20or%20(A%26amp%3BS)%3C%2FSTRONG%3E%20pre-exist%20anywhere%20prior%20it%20is%20a%20potential%20error.%20I've%20tried%20creating%20an%20array%20of%20sorted%2C%20unique%20combinations%20of%26nbsp%3B%3CSTRONG%3EA%26amp%3BS%3C%2FSTRONG%3E%20and%20validating%20against%20that%20but%20can%20not%20find%20a%20way%20to%20get%20Data%20Validation%20to%20accept%20both%20the%20validations.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%2C%20any%20help%2C%20ideas%20or%20reading%20would%20be%20a%20great%20assistance.%20My%20already%20balding%20head%20is%20getting%20worse!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20to%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETheOldPuterMan%20AKA%20John%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1067321%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1067346%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20validation%20using%20more%20than%20one%20formula%20for%20a%20single%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1067346%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3ESample%20file%20will%20be%20good%20to%20proffer%20position%20solutions%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1067405%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20validation%20using%20more%20than%20one%20formula%20for%20a%20single%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1067405%22%20slang%3D%22en-US%22%3EHello%20and%20thanks%20but%20I%20can't%20seem%20to%20find%20a%20link%20or%20download%20for%20a%20sample%20file%3F%3CBR%20%2F%3E%3CBR%20%2F%3ETheOldPuterMan%20AKA%20John%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1068192%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20validation%20using%20more%20than%20one%20formula%20for%20a%20single%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1068192%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F317593%22%20target%3D%22_blank%22%3E%40TheOldPuterMan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20your%20Reply%20window%20click%20on%20%3CSTRONG%3Ebrowse%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20799px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F161980iB809F77E22FE869C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
TheOldPuterMan
Contributor

I have to find a way to use multiple Data Validations in a single cell. Currently, I'm simply validating newly entered data against the contents of another column on the same row to see if it's identical. If it is not, all is ok, if it is not I issue a warning message and wait for user response. The most basic kind of validation. (=S=Q) with (S) being a Budget Amount and (Q) being the Actual Amount.

 

I need to expand that validation with a test to see if that value (S) appears anywhere else on the sheet in conjunction with the same Provider (A). Essentially if the Actual Amount (Q) is not equal to the Budgeted Amount (S) or the combination of (Provider (A) and Actual Amount (S))already exists in the sheet.

 

Essentially if (Q=S) or (A&S) pre-exist anywhere prior it is a potential error. I've tried creating an array of sorted, unique combinations of A&S and validating against that but can not find a way to get Data Validation to accept both the validations.

 

Please, any help, ideas or reading would be a great assistance. My already balding head is getting worse!

 

Thanks to all,

 

TheOldPuterMan AKA John

 

3 Replies
Highlighted
Hello,

Sample file will be good to proffer position solutions
Highlighted
Hello and thanks but I can't seem to find a link or download for a sample file?

TheOldPuterMan AKA John
Highlighted

@TheOldPuterMan 

In your Reply window click on browse

image.png

Related Conversations