Help
I have the names of all 50 states in the U.S., plus the District of Columbia, in a list in one worksheet. What's killing me is that Excel won't let me use this list as the source for a "Pick from List" validation criterion in a cell on another worksheet! Isn't there some way to use a set of data from one worksheet as a source of validation criteria in another worksheet?
The trick is to define the list of states as a named range, which you then can refer to in the Data Validation dialog box's Source field. Here's how:
Select the cells that contain your valid entries.
-
Choose Insert → Name → Define to display the Define Name dialog box (shown in Figure A).
Type a name for the data range in the "Names in workbook" field, click the Add button, and then click OK.
Flip to the other worksheet, select the cells to which you're adding the validation criteria, and choose Data → Validation.
On the Settings tab, open the Allow drop-down menu and select List.
In the Source field, type an equals sign followed by the name of the named range (e.g., =States)and then click OK. Incidentally, range names are not case-sensitive, so =States and =states are considered the same thing by Excel.
Excel Annoyances addresses all of the quirks, bugs, and hidden features found in the various versions of the Excel spreadsheet program. Broken down into several easy-to-follow categories such as Entering Data, Formatting, Charting, and Printing, Excel Annoyances uncovers a goldmine of helpful nuggets that you can use to maximize Excel's seemingly limitless potential.
