Remarks

• If you select F5 Special and check Data Validation, Excel will select all cells containing Data Validation settings.

• If you want to use the Data Validation drop-down list and the data source list is in a different sheet than the drop-down list, then you must name the list (as we did in our example) or the drop-down list will not work.

• If you use the dynamic-range technique described in Chapter 20, “The OFFSET Function,” then when you add new items (or delete items) from the data source list activating the drop-down list, the changes in the list will automatically be reflected in the drop-down box. See Problem 10. Also, if you name the data source list as an Excel Table (see Chapter 24, “Tables”) then changes in the list will be reflected in the drop-down list. This will work as long as you point to the list range and do not try to type in the Table name.

• Suppose you want to use a drop-down list to select a company you sell candy bars to. Then you want another drop-down list that you can use to select the list of candy bars you sell at the selected store. The problem is that the same set of candy bars might not be sold at each store. How do you create such a nested list selection? For example, suppose the stores were Target and CVS. Suppose you assign a range name of Target to the list of candy bars sold at Target and a range name of CVS to the list of candy bars sold at CVS. If the drop-down list for store selection is in, for example, A20, then you could create the appropriate drop-down list in cell B20 by clicking Data Validation and fill in for the list selected =INDIRECT(A20). As we discussed in Chapter 21, “The INDIRECT Function,” if A20 contains CVS then the list will key off the range CVS that contains all candy bars sold at CVS, and so on. See Problem 11.

• To clear Data Validations from a range, select the range containing the data validations. Then choose Data Validation from the Data tab and select Clear All.