[Solved] How to make validity list affect next cell list?

[lortel]

I am making a excel spread sheet for car parts estimate list. In it, I have year, make, model, and so on. In the make cell, I have a validity list of all the makes. If I select for example GM, I want to some how filter only those models in the next cell, in this case, the Model" cell to only show models made by GM and no other companies models.

Thanks a bunch!

[ken johnson]

Here's one way using named ranges...

Dependent Selection Lists.ods

(10.61 KiB) Downloaded 3027 times

Ken Johnson

[MrProgrammer]

Hi, and welcome to the forum.

Here's another way using IF statements:

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[gerard24]

Formula in source of validity is considered as array formula when reference is an array without entering as {array formula} (like Named formula).

So you can use IF statement directly in Data > Validity.
Source of the 2nd list =IF(Selections.A2:A11=A2;Selections.B2:B11;"")

Value FALSE is considered as blank cell in this case, you can reduced the formula like this :

Code: Select all

=IF(Selections.A2:A11=A2;Selections.B2:B11)

The source of the 3rd list is :

Code: Select all

=IF((Selections.A2:A11=A2)*(Selections.B2:B11=B2);Selections.C2:C11)

spate_list.ods

(9.52 KiB) Downloaded 1711 times

[keme]

Formula in source of validity is considered as array formula when reference is an array without entering as {array formula} (like Named formula).

So you can use IF statement directly in Data > Validity. ...

Thanks, Gerard24! I learned something new today.

The possibility to dynamically generate arrays should solve the OP's problem.