Forum Discussion
Data Validation Basis Another Cell Inputs
I have a simple sheet. Column A contains inputs that can be A, B C, D, E. The inputs are a drop down list. Column B inputs need to be restricted basis the input in Column A. So if A1 =A, B1 should have an input of the range100-12%,
If A1=B, B1 should have an input 120-140% and so on. Given the inputs in Column A can be dynamic and any of the 5 drop down values how do we restrict values in Column B dynamically to correspond to the input in Column A
Let's say you want to apply this to B2:B10
Select this range. B2 should be the active cell in the selection.
On the Data tab of the ribbon, select Data Validation.
Select Decimal from the Allow list.
Select between from the Data list.
In the Minimum box, enter the formula
=SWITCH(A2, "A", 100%, "B", 120%, "C", 140%, "D", 160%, "E", 180%)
In the Maximum box, enter the formula
=SWITCH(A2, "A", 120%, "B", 140%, "C", 160%, "D", 180%, "E", 200%)
Adjust the values as needed.
Click OK.
Result:
4 Replies
- This is also a good method, especially when the range for validation is a long one
Let's say you want to apply this to B2:B10
Select this range. B2 should be the active cell in the selection.
On the Data tab of the ribbon, select Data Validation.
Select Decimal from the Allow list.
Select between from the Data list.
In the Minimum box, enter the formula
=SWITCH(A2, "A", 100%, "B", 120%, "C", 140%, "D", 160%, "E", 180%)
In the Maximum box, enter the formula
=SWITCH(A2, "A", 120%, "B", 140%, "C", 160%, "D", 180%, "E", 200%)
Adjust the values as needed.
Click OK.
Result:
- Thank you, this was really helpful
