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

2. Hacking Excel’s Built-in Features > 16. Validate Data Based on a List on Ano...

Validate Data Based on a List on Another Worksheet

Data validation makes it easy to specify rules your data must follow. Unfortunately, Excel insists that lists used in data validation must appear on the same worksheet as the data being validated. Fortunately, there are ways to evade this requirement.

In this hack, we provide two methods you can use to validate data based on a list on another worksheet. The first method takes advantage of Excel’s named ranges (which are covered in more detail in Chapter 3), and the second uses a function call.

Method 1: Named Ranges

Perhaps the easiest and quickest way to overcome Excel’s data-validation barrier is by naming the range where the list resides. To create a named range, select the cells containing the list and enter a name in the Name box that appears at the left end of the Formula bar. For the purposes of this example, we will assume your range is called MyRange.


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


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