How to use a dynamic range for data validation lists from another sheet

I have an Excel sheet I use for managing my to do lists. It consists of 2 sheets

• the first one contains the actual actions items
• the second one contains some lists to be used in drop down boxes in the previous sheet

When setting up data validation you cannot simply refer to a range (e.g. “List!$A$2:$A$4") on another sheet. You will get an error saying:

You may not use references to other worksheets for data validation criteria.

A workaround consists in defining a Name as MrExcel explains. Go to the second sheet, highlight your list and give it a name with Formulas > Define Name. Make sure you set the scope to workbook rather than sheet!

Now you can refer to the name you just defined in the the Data Validation dialog.

This solution works very fine for lists of a fixed length (e.g. priorities). Things get a little less pretty if your list doesn’t have a fixed length (e.g. the people possibly assigned to a particular task):

• you can change the definition of your name every time something gets added/removed to/from your list
• you can set a range that is too big, resulting in a number of empty entries in the drop down
• you can use a dynamic range. This is definitely the best solution.

The offset function used for easy cut’n'paste.

   1: =OFFSET(List!$B$1;1;0;COUNTA(List!$B:$B)-1;1)

The drop down will now expand nicely every time you add value to your list.

I learned about dynamic ranges in Excel here (Thx sak1n1 for sending me that link!)