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 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): 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 → Sort & Filter → Advanced (pre-2007, 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 → ShowAll, select the list of unique objects, and cut and paste them into cell C2. Nowyou've got your list!

Select Formulas → Defined Names → Name Manager, click New(pre-2007, Insert → Name → Define) and in the Name: field, type the word Objects. In the Refers To: box, type the following formula and click OK (pre-2007, click Add, which will allow you to add another named range as below):

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

Nowclick the New(pre-2007, Add) button. In the Name: box, type the name ValList, and in the Refers To: box, enter $C$2:$C$5. Click Close. Now insert another worksheet, call it Sheet1, and roll up your sleeves as you put this strange data to work.

With Sheet1 still active, on the Formula tab, select Define Names → Name Manager, click New(pre-2007, Insert → Name → Define), enter CorrespondingList in the Name: field, and in the Refers To: field, enter this rather lengthy formula and then click OK:

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

Nowclick the New(pre-2007, Add) button and in the Name: box, type the name Val1Cell. In the Refers To: box, enter $D$6 and click OK. Click New again and in the Names: field type Val2Cell. In the Refers To: box, enter $E$6 and again click OK. Click Close (pre-2007, 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 under Data Tools options on the Data tab, and ensure you are on the Settings tab (pre-2007, 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 under Data Tools options on the Data tab (pre-2007, Data → Validation). 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.

When applying the data validation to E6, you will get the information message, "The source currently evaluates to an error. Do you want to continue?" Press Yes. This message occurs because D6 is currently blank.

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 nowhave one very user-friendly validation (pick) list, whose contents will change automatically based on the item chosen from the other pick list.