How to Base entries in a drop down list on a cell value

[rmccafferty]

I have a spreadsheet in which there will multiple drop down (combo) boxes. I understand how to build a combo box and refer to the range where the drop down lists lives. But in this series of drop downs, I want subsequent drop down options to be different depending upon what the selection was in the first drop down.

That is, if the first drop down has options 1, 2, 3 and 4,
I want the second drop down box to show a different list ( a different cell range) depending upon the selection in the first drop down.

So it the user selects 1 in the first drop down, I want them to see list A in the second drop down.
If the user selects 2 in the first drop down, I want them to see list B in the second drop down.
And so on.

For what it is worth, there will always be more than two options, so Select Case rather than IF would be required. I confess that I am more accustomed to Access than Excel and am not sure where or how to put any code. In looking around the web, I am not seeing any pages that tell how to do this for a combo box in Excel.

I assume that the condition would be written something like
SELECT CASE [range name of cell c1]
CASE [range name] = 1
Code for the range to use List A for the drop down
CASE [range name] = 2
Code for the range to use List B for the drop down
End Select

 

[SkipVought]

Hi,

I usually accomplish this via queries based on selection criteria.

You'll need a table that contains all this data.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[rmccafferty]

I would personally just go straight to Access myself and do the entire thing there. But I am trying to help someone who is not conversant in Access. I just looked up Microsoft Query quickly and it seems to be designed to work on external data, not for Excel data. Maybe what a quick search on the internet is not showing me what you would do. Or maybe I am too old to shift gears well. But I am not seeing how to use a query in Excel is terribly helpful.

 

[SkipVought]

Using MS Query to get data from Excel faq68-5829

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

It's one of the easiest methods: use a Data Validation In Cell Dropdown with a Dynamic Named Range list source, and MS Query with criteria as selected (no matter how deep) to generate the next selection list or to produce the final result table, for instance chart data. I'm 72, but I've been using this for 2 decades.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[rmccafferty]

Thank you for your persistence. It will likely be tomorrow before I can look into it with the attention it deserves. But I will do so with an open mind and a positive attitude.

 

[SkipVought]

It can open some new vistas.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]