Data Validation - Lists from Other Sheets / Workbooks

Alright!! We saw the basics and examples of using data validation in earlier posts. Now let’s see more practical examples of it.

Create a List Validation with reference from other sheet in same workbook:

Many a times, I enter the list items in same sheet but in a faraway hidden column. That is an easy way but few other times, it may not be possible to do so or you want to keep the source list some other sheet so that users cannot mess with it or there is a list available in other sheet and you simply cannot move it to the sheet where validation is required. Unless you are using Excel 2010, Excel will not allow you to take validation reference from other sheets.  Read on to see how we can work in this situation.

Below workaround applies to Excel 2007 and earlier versions. In Excel 2010 or later, you can take reference of source list from other sheets as you do in the same sheet.

We do this in 3 simple steps. Follow closely.

In Excel 2007

1. Create the Source List: Go to the sheet where your list items are entered (enter now if they are not already entered) and select all list items.
2. Create a Named Reference to the source list: Under Formulas ribbon, click Define Name. In the New Name dialogue box, give a name of your choice. But make sure the name does not begin with anything other than a letter or an underscore. Name should not contain spaces etc. For example: I give the name as MyList
3. Create Validation: Select cell(s) you want to use Data Validation and go to Data ribbon > Data Validation. In Data Validation dialogue box, under Settings Tab, select List in Allow: field. Under the Source, type = (equal sign) followed by the name you gave to your source list. In our example, I type =MyList

In Excel 2003 or earlier

1. Create the Source List: Go to the sheet where your list items are entered (enter now if they are not already entered) and select all list items.
2. Create a Named Reference to the source list: Under Insert menu, click Name and then Define. In the Define Name dialogue box, give a name of your choice. But make sure the name does not begin with anything other than a letter or an underscore. Name should not contain spaces etc. For example: I give the name as MyList
3. Create Validation: Select cell (s) you want to use Data Validation and go to Data menu > Data Validation. In Data Validation dialogue box, under Settings Tab, select List in Allow: field. Under the Source, type = (equal sign) followed by the name you gave to your source list. In our example, I type =MyList

That’s it. Check in the cell(s) and ensure you got all the list items appearing in the drop down.

Create a List Validation with reference from different workbook:

For this data validation to work, the workbook that contains the list must be open, in the same instance of Excel. Users will have to open workbook with the drop down lists, and workbook with the original source list.

Let’s think of an example. You have a workbook named List (with extension .xlsx in Excel 2007 or later and .xls in Excel 2003 or earlier) which contains a sheet with your list items. You also have another workbook named Validation (with extension .xlsx in Excel 2007 or later and .xls in Excel 2003 or earlier) and you want to apply validation in this book. Let’s see how we can do this.

1. Create a Named Reference to the Source List: Name the list items in workbook List with a Named Range called MyList. (Refer above example on how to name a range)
2. Open the workbook Validation (you can also create a new workbook). Open the Name Manager dialogue box by clicking on Formulas ribbon and click Define Name in Excel 2007 or later versions (In Excel 2003 or earlier, click Insert menu, click Name and then Define). In the New Name dialogue box (Define Name in Excel 2003 or earlier), type a name of your choice but something different from what you created in workbook List. For this example, I use a name MyListNew. Select Workbook under Scope. In Refers to: type the following carefully. Type = (equal sign), then the source workbook name and extension followed by ! (Exclamation mark), followed by the range name in the source workbook. In our example, I type =List.xlsx!MyList (In Excel 2007 or later) and =List.xls!MyList (In Excel 2003 or earlier).
3. Note: Type an apostrophe (') at the start and end of the referenced workbook name, if it contains a space character. For example: ='List 2014.xlsx'!MyList (In Excel 2007 or later) and ='List 2014.xls'!MyList (In Excel 2003 or earlier). Click OK
4. Create Validation: Select the cells in which data validation will be set. On the Ribbon, click the Data tab, then click Data Validation. In the Allow box, choose List. In the Source box, type an equal sign, then the list name created second time i.e. in Validation workbook, example: =MyListNew and click OK.

That’s it. Check in the cell(s) and ensure you got all the list items are appearing in the drop down. But remember, in order to use this validation drop down lists, both workbooks must be open.

I hope this helps you in work effectively with Data Validation. See you in another post!!