In a recent consulting assignment I had a tricky data validation problem. The customer wanted to have an either-or condition in the data validation, like this:
My initial reaction to this requirement was “hmm… that is not possible“. But before shooting the email back to client, I got curious and checked if excel data validation can actually do this. And of course we can do this in Excel with ease.
First see the demo of how this would work:
Now to the specifics:
- Select both cells where you want this data validation to be applied.
- Now go to data validation (Data Ribbon > Data Validation or Data Menu > Validation)
- Specify validation type as “Custom” and use a formula like COUNTA() to check count of cells with a value (see the illustration)
- Optional: Use Error Message settings to set a message you prefer.
- That is all. Now your Either Or Data Validation set up is done.
Download the example file:
Click here to download the example file with this kind of data validation setup. Play with it and learn how to do this on your own.
Learn more about Data Validation in Excel:
Read more about adding a drop down list validation or advanced data validation tricks or all of them.
Related: Writing XOR (either or) formulas in Excel
8 Responses to “How to Get “Either-Or” Condition in Data Validation?”
You should also use some kind of validation downstream that inserts some kind of error instead of doing the calculations.
=if(counta($I16,$K16)=1,,)
You can also expand it, like this
=COUNTA($A1:$D1)<3
That was cool, Chandoo. Good stuff.
On you, Chandoo. Hay, Chandoo- can-do! Never say never!
Hi, Chandoo.
In C4's validation I would just enter:
=LEN(e4)=0
and vice versa.
If you wanted one formula to do the entry simultaneously for both cell's validation:
=LEN(c4&e4)=0
I like to keep it short and focused. And LEN() is about the fastest function there is...
Regards,
Daniel Ferry
excelhero.com
Boy am I glad you've abbreviated 'please' to 'pls.' Just think of the keystrokes saved!
[...] example, Chandoo describes how to make ‘either-or’ type validation, meaning you can enter any one of two inputs, but not [...]
[...] Either-or condition in Excel [...]