Skip to Main Content
Excel Hacks
book

Excel Hacks

by David Hawley, Raina Hawley
March 2004
Beginner content levelBeginner
304 pages
7h 2m
English
O'Reilly Media, Inc.
Content preview from Excel Hacks

Chapter 2. Hacking Excel’s Built-in Features

Hacks #16-38

Although Excel comes with a wide variety of standard features for managing and analyzing data, the boundaries of these features are often frustrating. The hacks in this chapter provide numerous ways in which you can escape these boundaries and make Excel a much more powerful tool.

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.

Select the cell in which you want the drop-down list to appear and then select Data Validation. Select List from the Allow: field, and in the Source: box enter =MyRange. Click OK.

Because you used the named range, your list (even though ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Excel Hacks, 2nd Edition

Excel Hacks, 2nd Edition

David Hawley, Raina Hawley
101 Ready-To-Use Excel Macros

101 Ready-To-Use Excel Macros

Michael Alexander, John Walkenbach
Excel Annoyances

Excel Annoyances

Curtis D. Frye

Publisher Resources

ISBN: 059600625XErrata Page