Note: When using a named range for a data validation list, the named range must be defined as a reference to a range of cells, or it must be a formula like OFFSET or INDIRECT or INDEX that returns a reference.

Why? If you want to change the range, you only need to edit the defined name (via Formulas > Name Manager) rather than finding and editing all cells that use that particular data validation. You could use a reference for the Source field like =Sheet2!$A$1:$A$3, but I usually prefer to name the list. In the data validation dialog box, instead of entering the list manually, you enter a reference to the named range in the Source field as shown below: You can later hide the worksheet containing your list to keep your workbook looking nice and clean or to prevent a user from changing the list. In this case, I've named the range 'myList'. For example, let's say I have a separate worksheet with my list defined in cells A1:A3 as shown below. Instead of manually entering the list of items in the data validation dialog box, you can reference a range of cells. The more elegant approach is to use a reference to a range, or even better than that - a named range. The problem with this approach is that if you use this in a lot of cells and later want to update the list, you have to update all cells that use the list and there is a good chance you'll miss one. This approach is great for simple Yes/No options and other lists that appear only once in your spreadsheet. Entering the Source of a Drop Down List as a Comma-Delimited List

Insert drop down menu excel 2016