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.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
One comment on “Tom’s Tutorials For Excel: Using a Data Validation List From a Named Range
  1. Beth Holmwood says:

    Hello Tom,
    I hope you are doing well. I am in an Excel class and I am attempting to expand upon the very basic Excel functions I currently use, and data validation is one I have difficulty with. Your instructions are very helpful. Is it possible to use data validation when the information in the range to be validated consists of two columns (ie: B32:E41, when column B has been merged with C and D). I continue to receive the message ‘The list source must be a delimited list, or a reference to a single row or column’. There are two columns because there are two categories of products as selection options (Standard Flavors and Gourmet Flavors). I cannot figure out how to create a drop-down with the selections from both categories.
    Is there a way to do this?
    Thank you in advance for your patience with an Excel novice, and any guidance you can offer!
    Take care.

Leave a Reply

Your email address will not be published. Required fields are marked *