Excel Drop Down From List in Different Workbook

To make it easier for people to enter data, you can create drop down lists on an Excel worksheet.

Usually the source lists are stored in the same workbook as the drop downs. However, with named ranges, it is possible to use a list in a different workbook.

In the screen shot shown below, the original list is in the workbook at the left. The drop downs are in a different workbook, on the right.

DataValListLinked

There Is a Catch

My preference would be to keep the lists and drop downs in the same workbook, but if you need to have them in separate files, this technique will allow you to do that.

There’s one catch though, when using this data validation technique. The source workbook, which contains the original list, must also be open, when you are using the drop down lists.

So, it’s not a perfect solution, but it’s fairly easy to implement, as long as you remember to open the other workbook too.

Excel 2010 Instructions

I’ve just uploaded a video with instructions for this technique in Excel 2010, so you can see the steps for creating the named ranges and data validation drop down lists.

The written instructions for Excel 2007 and Excel 2010 are in this blog post: Data Validation List From Different Workbook

You can also watch the video on YouTube: Excel Drop Downs From List in Different Workbook

________________

You may also like...

4 Responses

  1. Can this technique incorporate auto-complete?

  2. Is there a copy of the VBA for this technique?

  3. ronald says:

    very helpful… thanks

  4. Doug says:

    Please help

    This is what i get when i press OK in the Data Validation window

    “You cannot use references to other woorksheets or woorkbooks for data validation criteria”

    Both wootkbooks are open

Leave a Reply to ronald Cancel reply

Your email address will not be published. Required fields are marked *