Combo to populate another combo (Excel 2003 SP3)

[steveh]

Good morning

I have a worldwide temperature chart which has a column which was populate by Country / City, for example Austria, Bludesch - Austria, Eggenberg I then have 2 combo's that look up where the goods are going from and the second where they are going to. However the powers to be have asked me to split the columns to Country and City which I have done, however what I cannot do (mainly because I don't understand the examples that I have Googled ) is to get a Combo 'Country of Origin' to populate a second Combo with Cities that only relate to the Country of Origin and then the same for Destination Country and Destination City. I hope this makes sense but I have attached a stripped down example to illustrate

Editted - wrong attachment

[StuartR]

Rory came up with a great solution for this problem in reply to a post on another forum. See this thread for details.

[HansV]

There's also a fairly elaborate step-by-step description and a downloadable sample file in Excel Data Validation -- Create Dependent Lists With INDEX.

[steveh]

Thanks Stuart and Hans

I have looked at Rory's example and cannot quite see how I can arrange the data in my tables to match. The first place I went was to Deberah over at Contextures but these are validated boxes not Combo's so such a fundemental change for me is quite a huge leap, plus arranging the data still puzzles me a bit. I have taken about 5 copies of my WB to save myself from myself and will dive in and have a go.

Have a good weekend, thanks

[HansV]

The idea was that you could have used validation dropdowns instead of ActiveX combo boxes, but here goes...

See the attached version. I used advanced filter with the Copy To / Unique Records Only to create a list of unique countries, and changed the definition of Countries to point to this list.
I cleared the ListFillRange property of the city combo boxes, and used code to populate them, e.g.

Code: Select all

Private Sub ComboBox4_Change()
  Dim r As Long
  With Me.ComboBox2
  .Value = ""
    .Clear
    For r = 48 To 62 ' adjust as needed
      If Range("C" & r) = Me.ComboBox4 Then
        .AddItem Range("D" & r)
      End If
    Next r
  End With
End Sub

Eileens-Sort by 2 lists.xls

[steveh]

Hi Hans Fantastic

Much more than I was after and helped me out of a hole but now I have time I will look at the other option with the validation dropdowns in case I need a non macro version in the future.

[steveh]

There's also a fairly elaborate step-by-step description and a downloadable sample file in Excel Data Validation -- Create Dependent Lists With INDEX.

Hi Hans

Thanks for this link, I have manually followed the instructions and created the example and it works fine, I must admit that I do not fully understand the explaination but I will try to create some other examples and hopefullly I will then grasp it.

Do you know if this method can be extended to cascade from one validated field to another (more than 1) say for example

First Validation = Region
Second Validation = Country (which we have in this example)
Third Validation = State
Fourth Validation = Companies in the State

I am guessing that I would need to make new validated lists that point towards the headings used in the master validation list but I just wanted to make sure that it would be possible before I dive in

[StuartR]

...Do you know if this method can be extended to cascade from one validated field to another (more than 1) say for example...

I have done this using the method that I posted a link to.

[HansV]

The example has 4 linked dropdowns: Region > Country > Area > City:

x223.png

If you download the sample workbook from the article, you can see the lists used for the dropdowns in the Lists sheet.
It wouldn't be hard to change Area to State and City to Company.

[steveh]

...Do you know if this method can be extended to cascade from one validated field to another (more than 1) say for example...

I have done this using the method that I posted a link to.

Hi Stuart

I looked at that one first and could not understand how it was all working so I have downloaded it for future 'homeworl' I was just working through what on the face of it looked like the easier option.

Thanks

[steveh]

The example has 4 linked dropdowns: Region > Country > Area > City:

x223.png

If you download the sample workbook from the article, you can see the lists used for the dropdowns in the Lists sheet.
It wouldn't be hard to change Area to State and City to Company.

Hi Hans

Thanks, I copied and printed the written instructions so that I could try and learn what was happening as I went along, what I did not realise is that it stopped short of populating 2 of the columns. I will download the workbook and take a look, thanks for pointing that out for me.

[StuartR]

...Do you know if this method can be extended to cascade from one validated field to another (more than 1) say for example...

I have done this using the method that I posted a link to.

Hi Stuart

I looked at that one first and could not understand how it was all working so I have downloaded it for future 'homeworl' I was just working through what on the face of it looked like the easier option.

Thanks

I know the feeling, it nearly burst my head the first time I read it too.