Tom’s Tutorials For Excel: Using a Data Validation List From a Named Range

Tom’s Tutorials For Excel: Using a Data Validation List From a Named Range

Here is how you can apply a Data Validation list for cells in one sheet, with that source list existing on another sheet. The process involves creating a named range for the source list, as shown by the following 7 steps.

Step 1 of 7: Select your source list, and from your keyboard press Ctrl+F3.


Step 2 of 7: In the Name Manager dialog box, click the New button.


Step 3 of 7: In the New Name dialog box…
• Enter a name for your range, in this example PositionTitles.
• Select Workbook as the Scope.
• Click the OK button.
• Note that Excel already entered your selected range address in the Refers to field.


Step 4 of 7: You’ll return to the Name Manager dialog box; just click the Close button.


Step 5 of 7:
• Select the worksheet where your range is located that will be Data Validated.
• Select the range of cell(s) that will be Data Validated.
• From your keyboard, press Alt+D+L to show the Data Validation dialog box.


Step 6 of 7: In the Data Validation dialog box…
• Click onto the Settings tab.
• For the Allow field, click the drop-down arrow and select List.
• In the Source field, enter the equals sign and your named source range from Step 3, example =PositionTitles.
• Click the OK button.


Step 7 of 7:
Your selected range is now Data Validated.
Click the drop-down arrow for your active cell, and select an item from the source list.