Conditional Data Validation in Excel
The user input fields in the figure are actually data validation lists. The user can make selections from a drop-down box rather than typing in the values. The Data Validation in cell E3 uses an interesting technique with an INDIRECT function to change its list depending on the value in E2.
The worksheet contains two named ranges. The range named Car points to E6:E7 and the range named Truck points to E10:E11. The names are identical to choices in the E2 Data Validation list. The following figure shows the Data Validation dialog box for cell E3. The Source is an INDIRECT function with E2 as the argument.
The INDIRECT function takes a text argument that it resolves into a cell reference. In this case, because E2 is “Truck”, the formula becomes =INDIRECT(“Truck”). Because Truck is a named range, INDIRECT returns a reference to E10:E11 and the values in those cells become the choices. If E2 contained “Car”, INDIRECT would return E6:E7 and those values would become the choices.
One problem with this type of conditional data validation is that when the value in E2 is changed, the value in E3 does not change. The choices in E3 change, but the user still has to select from the available choices or your formulas may return inaccurate results.