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

Hack 24 Create Validation Lists That Change Based on a Selection from Another List

Validation needs can vary depending on the
context in which the validation is used. However, you can create a
spreadsheet in which one validation list changes depending on what
you select in another.

To make this hack work, the first
thing you need to do is set up a worksheet with some data. On a clean
worksheet named Lists and located in cell A1, type the heading
Objects. In cell B1, type the heading
Corresponding List. In cells A2:A5, repeat the
word Can. In cells A6:A9, repeat the word
Sofa. In cells A10:A13, repeat the word
Shower. In cells A14:A17, repeat the word
Car. Then, starting with cell B2 and ending
with cell B17, enter the following words (corresponding to the
Objects list): Tin,
Steel, Opener,
Lid,
Bed, Seat,
Lounge, Cushion,
Rain, Hot,
Cold, Warm,
Trip, Journey,
Bonnet, and Boot.

In cell C1, enter the heading Validation List.
Next, to create a list of unique entries, enter the word
Can in cell C2, the word
Sofa in cell C3, the word
Shower in cell C4, and
the word Car in cell
C5.

You also can use the Advanced Filter to create a list of unique items. Select cells A1:A17, select Data Filter Advanced Filter, and then select Unique Records Only, Filter the List in Place. Click OK, and then select cells A2:A14 (which will include the hidden cells). Copy and paste them to cell A18. Select Data Filter Show All, select the list of unique objects, and cut and paste them into cell A2. Now you've got your list!

Select Insert Name Define, and in the Names in
Workbook: field, type the word Objects. In the
Refers To: box, type the following formula and click Add:

=OFFSET($A$2,0,0,COUNTA($A$1:$A$20),1)

In the Names in Workbook: box, type the name
ValList, and in the Refers To: box, enter
$C$2:$C$5.
Click Add. Now insert another worksheet, call it
Sheet1, and roll up your sleeves as you put
this strange data to work.

With Sheet1 still active, select Insert
Name Define. In the Names in Workbook: field,
enter the words CorrespondingList, and in the
Refers To: field, enter this rather lengthy formula and then click
Add:

=OFFSET(INDIRECT(ADDRESS(MATCH(Val1Cell,Objects,0)+1,2,,,"Lists")),0,0,COUNT
IF(Objects,Val1Cell),1)

Click the Names in Workbook: field and type the word
Val1Cell. In the Refers To: box, enter
$D$6 and click Add. Click the Names in
Workbook: field and type the word Val2Cell. In
the Refers To: box, enter $E$6 and again click
Add. Click OK to take yourself back to Sheet1 and then select $D$6.

This is a long process, but you are nearly done.

Select Data Validation
Settings. Select List from the Allow: box, and in the
Source: box, type
=ValList. Ensure that the In-Cell drop-down
checkbox is selected and click OK.

Select cell E6 and again select Data Validation
Settings. Select List from the Allow: box, and in the Source: box,
type =CorrespondingList. Then, ensure that the
In-Cell drop-down box is checked, and click OK.
Select one of the objects from the validation list in cell
D6, and the validation list in cell E6 will change automatically to
reflect the object you selected.

You now have one very user-friendly validation (pick) list, shown in
Figure 2-9, whose contents will change
automatically based on the item chosen from the other pick list. In
any cell or range of cells, you can use one pick list that houses up
to five separate lists.

Figure 2-9. Two corresponding validation lists in use