Displaying a Drop-Down List

In Excel 2007 the "Source" box for your List was changed to allow you to refer to cell references on other worksheets.
SS - 2003 & 2010 comparison
In Excel 2003 this was not possible using the built-in dialog box and this page describes two ways of getting round this problem.
You can still use these methods if you want to but it is not necessary if you are using Excel 2007 or later.
They are to use a named range or to use the INDIRECT function.

Referring to a Different Worksheet (Named Range)

Create a workbook level named range called "Book_List" which refers to cells "B2:B6".
Instead of using the cell reference in the source box you can just insert the named range.

There are two advantages to using a named range to define the list of items.

• The first is that it allows you to put the list on a different worksheet.

• The second is that you can make the named range dynamic so it expands automatically when new items are added below.

It is actually possible to create a named range that expands automatically to include new items.
For more details please refer to the Dynamic Named Ranges page.
The disadvantage of using a named range though is if any cell (within the named range) is moved or deleted then the named range will return #REF#

Referring to a Different Worksheet (INDIRECT Function)

The INDIRECT function allows you to use a text string to represent a cell reference.
Lets assume that your list is on Sheet1 and in cells "B2:B6"
Select a cell on a different worksheet and display the (Data > Validation) dialog box.
Enter the following formula into the "Source" box.

You do not have to include dollar signs and also to remember that if the worksheet names contain spaces then you must surround the worksheet name with single quotation marks.

Referring to a Different Workbook (Named Range)

For this to work the other workbook must be open.
Create a workbook level named range called "Book_List" which refers to cells "B2:B6".
In the workbook you want to display the drop-down list in you need to define a named range that refers to the workbook containing the list.

Referring to a Different Workbook (INDIRECT Function)

The INDIRECT function can also be used to refer to a range of cells in another "open" workbook.

If you want to refer to the same list from several different workbooks then you could save this list in your Personal.xls file. This is a workbook that is always open (but hidden)
Can you put the list in an addin ??

Important

However you enter (or type) the items they are always case sensitive.
It is possible to allow users to enter items that are not in the list by clearing the checkbox at the top of the Error Alert tab.
This is often done on a part of the worksheet that can either be hidden from view or will not be found (i.e. column IV).
It is possible to have the list on a separate worksheet although this requires the use of a named range.
If your list contains more than a couple of items it will probably be easier to maintain the list when it is types directly onto the active sheet.