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.

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,

Continue reading Excel Drop Down From List in Different Workbook