Excel Drop Down Help needed

Hi, welcome to the forum.

You need to create linked dropdowns by using Named Ranges, so that your second drop-down can refer to the Name selected in the first one.
Set up four columns, with the headers like this:

Countries | CountryA | CountryB | CountryC

In Column A, you have the following:

1 Countries (the header row)
2 CountryA
3 CountryB
4 CountryC

I've used 'CountryA' not just 'A' as these will be the Names later and it's not good to use just single letters.

In Column B, you have:

1 CountryA (the header row)
2 A1
3 A2
4 A3
5 A4

In columns C and D you have the same as B, but using CountryB/B1/B2/B3/B4 and CountryC/C1/C2/C3/C4 instead.

Now, name your ranges: select B2:B5 (the names of the CountryA cities) and type 'CountryA' into the Name box to the left of the formula bar. Repeat for C2:C5 and D2:D5. You can do the same for the countries by selecting A2:A4 with the name 'Countries'.

Now set up your first drop-down list. Click in the cell you want this in (let's say it's F2), go to the 'Data' tab and click 'Data Validation'. Select 'List' in the 'Allow' box then click in the 'Source' box and select the range A2:A5. If you Named this range, you can type '=Countries' instead (without the inverted commas). Click OK.

Now set up your second, linked, drop-down. Let's put it in G2. Click 'Data Validation' again and select 'List' again, then click in the 'Source' box. Now enter '=INDIRECT(F2)' (without the inverted commas).
When you click OK you'll probably get an error saying 'The Source currently evaluates to an error. Do you want to continue?' - click 'Yes' - this just means that you haven't selected anything in the first drop-down box yet, so Excel gets confused by the blank cell.

Now you can select your country in F2 and the choice in G2 will change to match it.

The attached file shows this working: Multiple linked dropdowns _ for praveen_excellor.xlsx


If there's a chance you will be adding more cities to the countries later, you could make the Named Ranges into dynamic ranges.
You can find instructions for doing that here: https://support.microsoft.com/en-us/kb/830287

Hope that all helps.

Originally Posted by Aardigspook

Hi, welcome to the forum.

You need to create linked dropdowns by using Named Ranges, so that your second drop-down can refer to the Name selected in the first one.
Set up four columns, with the headers like this:

Countries | CountryA | CountryB | CountryC

In Column A, you have the following:

1 Countries (the header row)
2 CountryA
3 CountryB
4 CountryC

I've used 'CountryA' not just 'A' as these will be the Names later and it's not good to use just single letters.

In Column B, you have:

1 CountryA (the header row)
2 A1
3 A2
4 A3
5 A4

In columns C and D you have the same as B, but using CountryB/B1/B2/B3/B4 and CountryC/C1/C2/C3/C4 instead.

Now, name your ranges: select B2:B5 (the names of the CountryA cities) and type 'CountryA' into the Name box to the left of the formula bar. Repeat for C2:C5 and D2:D5. You can do the same for the countries by selecting A2:A4 with the name 'Countries'.

Now set up your first drop-down list. Click in the cell you want this in (let's say it's F2), go to the 'Data' tab and click 'Data Validation'. Select 'List' in the 'Allow' box then click in the 'Source' box and select the range A2:A5. If you Named this range, you can type '=Countries' instead (without the inverted commas). Click OK.

Now set up your second, linked, drop-down. Let's put it in G2. Click 'Data Validation' again and select 'List' again, then click in the 'Source' box. Now enter '=INDIRECT(F2)' (without the inverted commas).
When you click OK you'll probably get an error saying 'The Source currently evaluates to an error. Do you want to continue?' - click 'Yes' - this just means that you haven't selected anything in the first drop-down box yet, so Excel gets confused by the blank cell.

Now you can select your country in F2 and the choice in G2 will change to match it.

The attached file shows this working: Attachment 420787


If there's a chance you will be adding more cities to the countries later, you could make the Named Ranges into dynamic ranges.
You can find instructions for doing that here: https://support.microsoft.com/en-us/kb/830287

Hope that all helps.

Hope that all helps.[/QUOTE]

@ praveen
You're welcome.