• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

2. Hacking Excel’s Built-in Features > 25. Force Data Validation to Reference a...

Force Data Validation to Reference a List on Another Worksheet

One of the options available in the data validation feature is the List option, which provides a nice drop-down list of specific items from which the user can choose. One drawback with data validation is that the moment you try to reference a list that resides on another worksheet, you will be told this is not possible. Luckily, you can make it possible by using this hack.

You can force data validation to reference a list on another worksheet using two different approaches: named ranges and the INDIRECT function.

Method 1: Named Ranges

Perhaps the easiest and quickest way to perform this task is by naming the range where the list resides. For this purposes of this exercise, we will assume you called that range MyRange. Select the cell in which you want this drop-down list to appear and select Data


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint