Part II: Working with Formulas and Functions
Validating conditional data
The user input fields in Figure 13.2 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 depend-
ing on the value in E2.
There are two named ranges in the worksheet. 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. Figure 13.3 shows the Data Validation dialog box for cell E3. The source is an
INDIRECT function with E2 as the argument.
FIGURE 13.3
Data validation using INDIRECT
The INDIRECT function takes a text argument that it resolves into a cell reference. In
this case, since 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
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.
Looking up values
When you have too many nested IF functions, your formulas can become long and hard to
manage. Figure 13.4 shows a slightly different setup to the auto-selector model. Instead of
hard-coding the results in nested IF functions, the results are entered into the cells next
to their properties (for example, Sedan is entered in the cell next to 4-door).
