Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 16 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 it resides
on another worksheet) now can be used for the validation list.

Method 2: the INDIRECT Function

The INDIRECT function
enables you to reference a cell containing text that represents a
cell address. You then can use that cell as a local cell reference,
even though it gets its data from another worksheet. You can use this feature to reference the
worksheet where your list resides.

Assume your list resides on Sheet1 in
the range $A$1:$A$8. Click any cell on a different worksheet where
you want to have this validation list (pick list) appear. Then select
Data Validation and choose List from the Allow: field. In
the Source: box, enter the following code:

=INDIRECT("Sheet1!$A$1:$A$8")

Ensure that the In-Cell drop-down box is checked and click OK. The
list that resides on Sheet1 should appear in your drop-down
validation list.

If the name of the worksheet on which your list resides contains
spaces, you need to use the INDIRECT function in
the following way:

=INDIRECT("'Sheet 1'!$A$1:$A$8")

The difference here is that you used
a single apostrophe immediately after the first quotation mark and
another single apostrophe immediately before the exclamation point.

It is a good idea to always use the single apostrophe, regardless of whether your sheet name contains spaces. You still will be able to reference a sheet with no spaces in its name, and it makes it easier to make changes later.

The Pros and Cons of Both Methods

Named ranges and the INDIRECT function each have
an advantage and a disadvantage.

The advantage to using a named range is
that changes you make to the sheet name will have no effect on the
validation list. This highlights the
INDIRECT function's
disadvantagenamely, that any change you make to the sheet name
will not be reflected automatically within the
INDIRECT function, so you will have to manually
change the function to correspond to the new sheet name.

The advantage to using the
INDIRECT function is that if the first cell/row or
last cell/row is deleted from the named range, the named range will
return a #REF! error. This highlights the disadvantage to using named
ranges: if you delete any cells or rows from within a named range,
those changes will not affect the validation list.