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
2 Responses to “Celebrating the Lookup Formulas – VLOOKUP Week @ Chandoo.org”
How did you arrive or formula when you ask me who made more sales Jackie or Jamie?? I could not figure the formula out yet??
@Frank
You need to sum up the sales for Jackie and compare them to the sales of Jamie over the same period.
The practical parts depends on how and what data you have and how it is arranged.
But it will either involve a Sum, Sumif, Sumifs or Sumproduct to accummulate the sales for each within a specified date range.