List of questions depending on cell selection

Giofan

New Member
Joined
Apr 4, 2012
Messages
14
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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,511
Office Version
  1. 365
Platform
  1. Windows
If you need further detail about the process, there is a pretty detailed description, images and video here.
 

Giofan

New Member
Joined
Apr 4, 2012
Messages
14
Thanks so much for this, this is perfect!!
This is such a useful forum, I'm really glad I found it.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,687
Messages
5,637,832
Members
416,984
Latest member
dee10

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top