How to Create Dynamic Data Validation Rules Based on Another Cell in Excel
Question details
The user needs to restrict percentage inputs in one column dynamically based on the category selected in another column.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up data validation rules where acceptable input limits change dynamically based on the value of a neighboring cell.
- Observed behavior
- A need to implement a row-relative data validation rule using logical formulas to enforce dynamic minimum and maximum limits.
Ensure your data is organized into clear columns and determine the minimum and maximum boundaries for each category before applying validation rules.
Apply Dynamic Data Validation Using the SWITCH Function
This method uses the SWITCH function within the Data Validation dialog to dynamically calculate minimum and maximum limits based on the value in an adjacent column.
When applying data validation across multiple rows, using a relative cell reference (like A2 instead of $A$2) ensures that each row calculates its own specific limits based on the corresponding category in its row.
Highlight the range of cells where you want to apply the validation (e.g., B2:B10). Ensure that the top cell (B2) is your active cell during the selection.
Go to the 'Data' tab on the ribbon and click on 'Data Validation' in the Data Tools group.
In the Settings tab of the Data Validation dialog, change the 'Allow' dropdown to 'Decimal' and set the 'Data' dropdown to 'between'.
In the 'Minimum' field, enter your logical formula. For example: =SWITCH(A2,"A",100%,"B",120%,"C",140%,"D",160%,"E",180%)
In the 'Maximum' field, enter the corresponding upper limit formula. For example: =SWITCH(A2,"A",120%,"B",140%,"C",160%,"D",180%,"E",200%)
Click 'OK' to save and apply the rule to your selected range.
Easily Set Up Dynamic Data Validation in WPS Spreadsheet
WPS Office provides a highly compatible Spreadsheet tool that perfectly supports advanced functions like SWITCH and IF inside Data Validation rules. You can manage complex data entry requirements with ease.
- 1. Select target column: Open your workbook in WPS Spreadsheet and select the column where you want to restrict input.
- 2. Navigate to Validation: Navigate to the 'Data' tab and select 'Validation'.
- 3. Configure criteria and formulas: Choose your criteria (e.g., Decimal, Whole number) and input your dynamic formula (like SWITCH or IF) into the Min/Max fields.
- 4. Apply and test: Click 'OK' to activate the dynamic validation rule and test it by entering data.
Frequently Asked Questions
Can I use the IF function instead of SWITCH for dynamic data validation?
Yes, you can use nested IF functions in the Data Validation fields (e.g., =IF(A2="A", 100%, IF(A2="B", 120%, 0))). However, for multiple categories, the SWITCH function is often easier to read and maintain.
Why is my dynamic data validation applying the rule to the wrong row?
This usually happens if you use absolute references (like $A$2) instead of relative references (like A2). Ensure you remove the dollar signs from the row number in your formula so the rule adjusts correctly for each subsequent row.
Does dynamic data validation automatically update existing data?
No, data validation only restricts new data entry. If you already have data in the cells and you change the validation rules, the existing data will not be removed or altered automatically. You can use the 'Circle Invalid Data' feature under the Data Validation menu to highlight entries that violate the new rules.
Can I use a VLOOKUP formula inside Data Validation for dynamic limits?
Yes, you can use VLOOKUP or XLOOKUP referencing a separate lookup table to fetch the minimum and maximum boundaries based on the adjacent cell's value. This is highly recommended if you have many categories, as it avoids hardcoding values into the validation formula.
