To make this tutorial 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):
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:
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:
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 the figure, 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.