List of questions depending on cell selection

[Giofan]

Hi,

I wonder if there is a way of Displaying a list, depending on the selection of a given cell. For example, if A1 has a drop down list of Cars and Cells B1:B5 displays the name of types of the selected Cars (If A1 = BMW then B1:B5 = X1, X3, X5, A3 & Z4 respectively, and if A1 = Mercedes then B1:B5 = SLS AMG, S600, SL Class, Maybach S600 & AMGC63 respectively).

I want to see if this can be done entirely with formulas as I have no VBA experience. Any help would be greatly appreciated.

Many thanks in advance.

 

[pgc01]

Hi

You just have to add a named range for each of the options.

Ex.

In Sheet1!Z1:Z5, write X1, X3, X5, A3 & Z4.

Create the named range
Name: BMW
Refers to: =Sheet1!$Z$1:$Z$5

In B1 write:

=IFERROR(INDEX(INDIRECT($A$1),ROWS($B$1:B1)),"")

Copy down for as many rows as the maximum number of options (for ex. till B10)


Now if you choose BMW in A1, you'll see the 5 models in cells B1:B5

 

[Peter_SSs]

If you need further detail about the process, there is a pretty detailed description, images and video here.

 

[Giofan]

Thanks so much for this, this is perfect!!
This is such a useful forum, I'm really glad I found it.

 

[pgc01]

I'm glad it helped!