Leading the way in Microsoft Office Development

Excel

Displaying a Drop-Down List

You do not have to use specific cell references on the same worksheet for your list of items.

There are two alternative methods that can be used if you want to have the list on a different worksheet.

They are to use a named range or to use the INDIRECT() function.

Please refer to the Drop-Down List page if you want the drop-down list to appear on the same worksheet as the cells that contain the list.

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 ??

Things to Remember

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.