Listbox value depending on other listbox value

[noord453]

Hi,

I use in 1 form 2 listboxes, one to select a country, and one to select a
city. If the user selects a county, automaticly from the list a default city
shoudl be made visible in the second listbox. At this moment the default is
not set, possible because the value of the first listbox is not yet set in
the worksheet?

How to make this happen?

regards
Gerard

 

[Patrick Molloy]

Indirect() is the function

example
4 tables.
Table 1, range name COUNTRIES, list is ENGLAND, FRANCE , USA
Table 2, range name ENGLAND, list is London, Norwich, Liverpool, Manchester
Table 3, range name FRANCE, list is Paris, Rheims, Lyon
Table 4, range name USA, list is New York, Los Angeles, Boston, Orlando

in a cell, named SelectedCountry, add data validation, select List and
=Countries
in another cell, SelectedCity, add data validation, choose list and the
source is =INDIRECT(SelectedCountry)

add a CHANGE event to the sheet, that clears SelectedCity if the changed
cell was SelectedCountry

Hope this gives you an idea



http://www.xl-expert.com/IndirectValidation.htm

 

[Kassie]

You need to set up a list of towns for each country. Name the list of towns
after the country they relate to. The list of countries you could name
Countries. For each country you would then have a seperate list. Say you
have Argentina, Equador, Poland, Russia, South Africa. The source for
listbox1 would then be Countries.

In your second listbox, indicate the source as =INDIRECT(Listbox1). In this
way, it would read the list from the range name pertaining to that country.

--
HTH

Kassie

Replace xxx with hotmail

 

[noord453]

Patrick,

Thanks for your answer. Please let me explain more my challange.

In a form, I want to select a county, this selection is stored in sheet1
cell C3, this cell is named country. In sheet2 cell C5 a vlookup is done to
find a match in a table which looks like this:
=IF(country<>"";VLOOKUP(country;'Parameters - lists'!G2:I200;3;FALSE);"")

Where Parameters lists, is a sheet, in which I have stored all the lists.

Regards
Gerard

Country Customer Centre
Netherlands Amsterdam
U.K. Bristol
France Amsterdam
Belgium Bristol
Germany Bonn

In the same form the second listbox must show, the corresponding customer
centre, based on the value of sheet2 cell C5

So I am looking for the command in VB to make this happen because now
because the form is not completed yet, the county value is not set in sheet1
C3, or I am looking for some programming in VB to do the VLOOKUP. The VLOOKUP
command used in Sheet2 C5 is as follows: