Sometimes we need to input data based on multiple criteria in our Excel datasheet. Data entries that don’t follow our desired conditions may create problems in our worksheet. So we expect to have an error message if we input the wrong entries. In this article, we’ll show you some examples of applying Custom Data Validation for Multiple Criteria in Excel.
4 Examples to Apply Custom Data Validation for Multiple Criteria in Excel
1. Apply Custom Data Validation for Multiple Criteria in One Excel Cell
The OR function finds out if any of the arguments or conditions in the argument are true or not. We use this OR function when we have to deal with multiple criteria. To illustrate, I’m going to use a sample dataset as an example. For instance, the following dataset has two criteria: Criteria 1 contains a list of products, and Criteria 2 has 2 specific dates. Here, we’ll apply the OR function in cell B5 so that the input meets any of the criteria. Therefore, follow the below steps to apply Custom Data Validation for Multiple Criteria in Excel.
STEPS:
- First, under the Data tab, select Data Validation from the Data Tools group.
- As a result, the Data Validation dialog box will pop out.
- Then, under the Settings tab, select Custom in the field: Allow.
- After that, in the Formula box, type the formula:
=OR(COUNTIF($D$5:$D$10,B5)=1, AND(B5>=E5,B5<=E6))- Subsequently, press OK.
Here, the AND function checks whether the date input is between E5 (1-Feb-2022) & E6 (1-Mar-2022). The COUNTIF function checks for the B5 text value to be from the range D5:D10. And at last, the OR function checks if the B5 cell input satisfies any of the specified conditions.
- Finally, you can enter any of the products from Criteria 1 or any date between the specified dates.
- But, if you enter anything which doesn’t follow either of the conditions, you’ll get an error dialog box.
Read More: How to Apply Multiple Data Validation in One Cell in Excel
2. Use Custom Data Validation for Multiple Criteria in Selected Cells
Additionally, we can apply the data validation in a range of cells instead of just a single cell shown in the previous example. In the below dataset, we have 2 criteria: Criteria 1 has a list of products, and Criteria 2 has a number input which is 50. In this example, we’ll apply the Custom Data Validation for Multiple Criteria in the range B5:B10. So, learn the steps given below to input any data that follows either Criteria 1 or Criteria 2.
STEPS:
- Firstly, select the range B5:B10.
- Next, go to Data ➤ Data Tools ➤ Data Validation. Consequently, the Data Validation dialog box will pop out.
- There, under the Settings tab, select Custom in the Allow field, and in the Formula box, type:
=OR(B5<$E$5,COUNTIF($D$5:$D$10,B5)=1)- Afterward, press OK.
The COUNTIF function checks whether the input is from the range D5:D10 and will count only if it is present in the range. The next criteria are to see if the input is Smaller than E5 (50). At last, the OR function checks if the inputs in the range B5:B10 satisfy any of the conditions.
- Eventually, you’ll be able to input valid data following the conditions.
- For this example, oven and 15 in cells B5 and B6 are valid. But, when we try to input 59, it returns an error message as 59 is Greater than 50.
3. Prevent Duplicate Entries in Excel and Limit the Number of Characters with Custom Data Validation
Moreover, we can prevent duplicate entries and also limit the number of characters by applying Custom Data Validation. For instance, the below dataset represents the ID, Salesman, and Product of a company. Here, we’ll complete the list of IDs with number entries of 3 digits and without repeating any values. Hence, learn the process given below to perform the task.
STEPS:
- In the beginning, select the range B5:B10.
- Then, go to Data ➤ Data Tools ➤ Data Validation.
- As a result, a dialog box will appear.
- Under the Settings tab, select Custom in Allow. Next, in the Formula box, type the formula:
=AND(COUNTIF($B$5:$B$10,B5)<=1, ISNUMBER(B5), LEN(B5)=3)- Press OK.
In brief, the ISNUMBER function takes number inputs. The LEN function checks for the input for only 3 digits. The COUNTIF function prevents any duplicate entries. And lastly, the AND function checks whether the entries satisfy all the conditions or not. It’ll be valid only if the entries satisfy all the conditions.
- In the end, you can input any valid data maintaining all the criteria.
- Otherwise, it’ll result in an error dialog box.
Read More: How to Remove Data Validation Restrictions in Excel
4. Allow Dates between Two Dates by Using Custom Data Validation for Multiple Criteria in Excel
In our last example, we’ll show how to allow date entries between two given dates. In the following dataset, we have a start date in cell D12 and an end date in cell D13. We have to input the Dispatch Date of each Salesman between those two dates. Therefore, follow the below process.
STEPS:
- Select the range D5:D10 at first.
- Now, select Data ➤ Data Tools ➤ Data Validation.
- The Data Validation dialog box will pop out.
- Under the Settings tab, select Custom in the Allow In the Formula box, type:
=AND(D5>=$D$12, D5<=$D$13)- Then, press OK.
Here, the AND function checks whether the date entries fall between the dates mentioned in cells D12 and D13.
- Hence, you’ll be able to input dates following the criterion. But as soon as you type any invalid date that doesn’t satisfy the condition, you’ll get an error message.
- In this example, although 29-02-2022 falls between the specified dates, it shows an error box. This is because 2022 is not a leap year and thus, 29th February doesn’t exist.
Download Practice Workbook
Download the following workbook to practice by yourself.
Conclusion
Henceforth, you will be able to apply Custom Data Validation for Multiple Criteria in Excel with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Use IF Statement in Data Validation Formula in Excel
- How to Use Custom VLOOKUP Formula in Excel Data Validation
- Data Validation Based on Another Cell in Excel
- How to Copy Data Validation in Excel
- How to Remove Blanks from Data Validation List in Excel
<< Go Back to Excel Custom Data Validation | Data Validation in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
I would like to create a spreadsheet for our three companies. When choosing the company, I am looking to have the tables listed for that company. Is this possible? I have been trying to understand the steps to work this out but not finding what I am looking for.
Hi Jenn,
Based on your comment, it seems you are looking for a way to create a spreadsheet that displays different tables based on the selected company.
To address your requirement, one approach is to use Excel’s “Data Validation” feature combined with “Index” and “Offset” functions. Here’s a potential step-by-step solution:
1. Set up your spreadsheet with separate tables for each company, each in a different range of cells.
2. Create a list of company names (e.g., in a dropdown) where the user can select the desired company.
3. Assign data validation to the cell where the user selects the company, limiting the input to the list of company names.
4. Next, use the “Index” and “Offset” functions to display the corresponding table based on the selected company.
Here’s an example of how this could work:
1. Create separate tables for each company, each in a different range of cells (e.g., Company A in cells A1:D10, Company B in cells A15:D24, Company C in cells A29:D38).
2. Create a dropdown list of company names (e.g., in cell A50) using Excel’s Data Validation feature.
3. Use the “Index” and “Offset” functions to retrieve the appropriate table based on the selected company. For example, in cell A55, you could use the following formula:
=INDEX($A$1:$D$10, OFFSET($A$1:$A$10, MATCH($A$50,$A$1:$A$10,0)-1, 0, ROWS($A$1:$A$10), COLUMNS($A$1:$D$10)))This formula will retrieve the table for the selected company dynamically.
Now, when you select a company from the dropdown list in cell A50, the corresponding table will be displayed in cell A55 and automatically update based on the selection.
Please note that the specific ranges and formulas may need to be adjusted based on the actual structure and layout of the spreadsheet. However, this approach should provide a starting point to achieve the desired functionality of displaying different tables based on the selected company in Excel.
Regards,
ExcelDemy