Page 89 - Excel Timesaving Techniques for Dummies

16_574272 ch14.qxd 10/1/04 10:40 PM Page 74
74
Technique 14: Ensuring Accurate Data Entries with Data Validation
To use the data validation List setting to restrict the Copying data validation settings
data entry in this new table to any of the cities on
this list, I then followed these steps: After assigning your data validation criteria to a par-
ticular cell, you can then copy these criteria to all
1. Position the cell pointer in the first blank cell the cells in the vicinity that need to use the same
to use this data validation setting (B3 in this restrictions. To do this, you follow these steps:
example).
1. Copy the cell with the data validation to the
2. Choose Data➪Validation to open the Data Clipboard.
Validation dialog box with the Settings tab
selected. (Choose Edit➪Copy or press Ctrl+C.)
3. Select List in the Allow drop-down list box. 2. Select the cell range into which to copy the
data validation criteria.
4. Put the Insertion point in the Source text box
and then drag through the range of cells con- 3. Choose Edit➪Paste Special or to open the Paste
taining the list of valid entries (J3:J7 in this Special dialog box.
case).
4. Click the Validation option button (see
Because Excel automatically selects the In-cell Figure 14-4) and click OK.
Dropdown check box, the program will add a
drop-down button that reveals this list of cities
whenever the user selects the cell.
To force users to select one of the cities —
making it invalid to leave the cell blank — clear
the Ignore Blank check box.
5. Click OK to close the Data Validation dialog box.

After the Data Validation dialog box closes, Excel
adds a drop-down button to the right side of the cur-
rent cell. To select one of the cities on the list from
its menu, click this button and then click the name of
the city to enter in cell B3, as shown in Figure 14-3.

• Figure 14-4: Copying the data validation criteria down the
column with Paste Special.

For example, to copy the data validation criteria that
cause the cell to display the cities drop-down list
box from the original cell B3 down the column to
the range B4:B15, I copied the contents of B3 to the
Clipboard and then selected the range B4:B15. Next,
• Figure 14-3: Selecting a city from the cell’s new I opened the Paste Special dialog box, clicked the
drop-down list. Validation option button, and clicked OK. As a result,