Excel Spreadsheets: you are equipped with far too much, it is nice but overwhelming. More functionality is not always better. Certainly no longer simple and ‘easy to use’.

Data validation across spreadsheets is not a capability, while it should be. In order to validate data across spreadsheets on must ‘force’ the action. Force!

Thank You Brainbell.com writer for providing me with helpful, useful, simple, straight-to-the-point data. Other bloggers provided faulty / misleading information, way too much info and of little help.

Excerpt:

Force Data Validation to Reference a List on Another Worksheet

One of the options available in the data validation feature is the List option, which provides a nice drop-down list of specific items from which the user can choose. One drawback with data validation is that the moment you try to reference a list that resides on another worksheet, you will be told this is not possible. Luckily, you can make it possible by using this tutorial.

You can force data validation to reference a list on another worksheet using two different approaches: named ranges and the INDIRECT function.

Method 1: Named Ranges

Perhaps the easiest and quickest way to perform this task is by naming the range where the list resides. For [the purpose] of this exercise, we will assume you called that range MyRange. Select the cell in which you want this drop-down list to appear and select Data Validation. Select List from the Allow: field, and in the Source: box, enter =MyRange. Click OK. Your list (which resides on another worksheet) can now be used for the validation list.

(Source: BrainBell.com)

For further assistance:

When utilizing ‘Named Ranges’ you must identify your range (list items) and name them (range name) before Validating.

Steps: (i) menu Insert tab, (ii) hover Name, select -> Define…, (iii) in the Names in Workbook: box, enter the ‘title’ you assigned to the column and in the Refers to: box, select your defined range (cells with data you want to account for).

‘title’ will be your range (from the above example: MyRange) and your defined range will be the items that show up in your final drop-down list.

Enjoy!