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.

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.