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

image image

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!

image

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

image

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.

image

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!)

http://blog.koenvermoesen.be/about-koen/

Tagged with:
Posted in Software
2 comments on “How to use a dynamic range for data validation lists from another sheet
  1. RaiulBaztepo says:

    Hello!
    Very Interesting post! Thank you for such interesting resource!
    PS: Sorry for my bad english, I’v just started to learn this language ;)
    See you!
    Your, Raiul Baztepo

  2. […] already mentioned that I use excel to manage task lists from time to time. Sometimes reality catches up and a task is […]

Comments are closed.

IT Pro Summer GP
IT Pro Summer Grand Prix
MVA
Microsoft Virtual Academy
Pro-Exchange
Pro-Exchange / Pro-Lync / Pro-Office365
MEET

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 158 other followers

%d bloggers like this: