Dropdown values depending on other dropdown value with data from another workbook

S

somers

New Member
Joined
May 12, 2022
Messages
20
Office Version
  1. 365
Platform
  1. Windows
I have 2 workbooks.

In workbook1 i have the country names in column b, B1 is the title, B2-B24 countrynames (not unique names).
In column D i have the corresponding cities of these countries, D1 is the title and following rows are the citynames.

I want to create 2 dropdowns in workbook 2, in cell F6 a dropdown with the unique values of the countries and in cell F7 a second dropdown that only shows the cities corresponding to the country selected in the dropdown of cell F6.

I have tried many things with defining names and data validations but nothing seems to work correctly.
Any simple method for this "easy" task?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
somers said:
I have 2 workbooks.

In workbook1 i have the country names in column b, B1 is the title, B2-B24 countrynames (not unique names).
In column D i have the corresponding cities of these countries, D1 is the title and following rows are the citynames.

I want to create 2 dropdowns in workbook 2, in cell F6 a dropdown with the unique values of the countries and in cell F7 a second dropdown that only shows the cities corresponding to the country selected in the dropdown of cell F6.

I have tried many things with defining names and data validations but nothing seems to work correctly.
Any simple method for this "easy" task?
Click to expand...
Can you please use XL2BB to show us workbook 1 with representative data that can be used for developing and testing a solution?
 
Upvote 0
Herakles said:
Can you please use XL2BB to show us workbook 1 with representative data that can be used for developing and testing a solution?
Click to expand...
I can't download XL2BB due to restrictions on my laptop.
Below the excel in table form.

NrCountryCity
-StandaardStandaard
1BelgiëBrussel
2SpanjeBarcelona
3FrankrijkParijs
4NederlandAmsterdam
5DuitslandFrankfurt
6DuitslandHannover
7DuitslandDüsseldorf
8ItaliëMilaan
9DuitslandMünchen
10FrankrijkLyon
11DuitslandBerlijn
12UAEDubai
13SpanjeMadrid
14FrankrijkNanten
15OostenrijkWenen
16Verenigd KoninkrijkLonden
17DuitslandHamburg
18FrankrijkCannes
19FrankrijkLilles
20PolenWarschau
21BelgiëGent
22BelgiëKortrijk


The result in workbook 2 would look like that:

If in Dropdown 1 "Duitsland" is selected, Dropdown 2 gives only the cities that are in "Duitsland" (ex. Frankfurt, Hannover, Dusselorf, ...)
 
Upvote 0
Book1
ABCDEFGHI
1NrCountryCity
2-StandaardStandaard
31BelgiëBrussel
42SpanjeBarcelona
53FrankrijkParijsUnique CountriesCites based on choice
64NederlandAmsterdamDuitslandHannoverBelgiëFrankfurt
75DuitslandFrankfurtSpanjeHannover
86DuitslandHannoverFrankrijkDüsseldorf
97DuitslandDüsseldorfNederlandMünchen
108ItaliëMilaanDuitslandBerlijn
119DuitslandMünchenItaliëHamburg
1210FrankrijkLyonUAE
1311DuitslandBerlijnOostenrijk
1412UAEDubaiVerenigd Koninkrijk
1513SpanjeMadridPolen
1614FrankrijkNanten
1715OostenrijkWenen
1816Verenigd KoninkrijkLonden
1917DuitslandHamburg
2018FrankrijkCannes
2119FrankrijkLilles
2220PolenWarschau
2321BelgiëGent
2422BelgiëKortrijk
Sheet1
Cell Formulas
RangeFormula
H6:H15H6=UNIQUE(B3:B24)
I6:I11I6=FILTER(C3:C24,B3:B24=F6)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
F6:G6List=H6#
 
Upvote 0
Book1
ABCDEFGHI
1NrCountryCity
2-StandaardStandaard
31BelgiëBrussel
42SpanjeBarcelona
53FrankrijkParijsUnique CountriesCites based on choice
64NederlandAmsterdamDuitslandBelgiëFrankfurt
75DuitslandFrankfurtHannoverSpanjeHannover
86DuitslandHannoverFrankrijkDüsseldorf
97DuitslandDüsseldorfNederlandMünchen
108ItaliëMilaanDuitslandBerlijn
119DuitslandMünchenItaliëHamburg
1210FrankrijkLyonUAE
1311DuitslandBerlijnOostenrijk
1412UAEDubaiVerenigd Koninkrijk
1513SpanjeMadridPolen
1614FrankrijkNanten
1715OostenrijkWenen
1816Verenigd KoninkrijkLonden
1917DuitslandHamburg
2018FrankrijkCannes
2119FrankrijkLilles
2220PolenWarschau
2321BelgiëGent
2422BelgiëKortrijk
Sheet1
Cell Formulas
RangeFormula
H6:H15H6=UNIQUE(B3:B24)
I6:I11I6=FILTER(C3:C24,B3:B24=F6)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
F6List=H6#
F7List=I6#
 
Upvote 0
kevin9999 said:
Book1
ABCDEFGHI
1NrCountryCity
2-StandaardStandaard
31BelgiëBrussel
42SpanjeBarcelona
53FrankrijkParijsUnique CountriesCites based on choice
64NederlandAmsterdamDuitslandBelgiëFrankfurt
75DuitslandFrankfurtHannoverSpanjeHannover
86DuitslandHannoverFrankrijkDüsseldorf
97DuitslandDüsseldorfNederlandMünchen
108ItaliëMilaanDuitslandBerlijn
119DuitslandMünchenItaliëHamburg
1210FrankrijkLyonUAE
1311DuitslandBerlijnOostenrijk
1412UAEDubaiVerenigd Koninkrijk
1513SpanjeMadridPolen
1614FrankrijkNanten
1715OostenrijkWenen
1816Verenigd KoninkrijkLonden
1917DuitslandHamburg
2018FrankrijkCannes
2119FrankrijkLilles
2220PolenWarschau
2321BelgiëGent
2422BelgiëKortrijk
Sheet1
Cell Formulas
RangeFormula
H6:H15H6=UNIQUE(B3:B24)
I6:I11I6=FILTER(C3:C24,B3:B24=F6)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
F6List=H6#
F7List=I6#
Click to expand...
Thanks this works!
To make this work in another workbook you have to put Cells with datavalidation in NameManager and then refer to those in data validation list.
 
Upvote 0
somers said:
Thanks this works!
Click to expand...
Good to hear (y)
somers said:
To make this work in another workbook you have to put Cells with datavalidation in NameManager and then refer to those in data validation list.
Click to expand...
Is that a question? If you mean you intend naming the top cells in the data validation lists (i.e. the cells with the Unique & Filter formulas) then that's OK - just remember to add a hash # at the end of the names to allow for the spill. Something like this:
Book1
ABCDEFGHI
1NrCountryCity
2-StandaardStandaard
31BelgiëBrussel
42SpanjeBarcelona
53FrankrijkParijsUnique CountriesCities based on choice
64NederlandAmsterdamDuitslandBelgiëFrankfurt
75DuitslandFrankfurtDüsseldorfSpanjeHannover
86DuitslandHannoverFrankrijkDüsseldorf
97DuitslandDüsseldorfNederlandMünchen
108ItaliëMilaanDuitslandBerlijn
119DuitslandMünchenItaliëHamburg
1210FrankrijkLyonUAE
1311DuitslandBerlijnOostenrijk
1412UAEDubaiVerenigd Koninkrijk
1513SpanjeMadridPolen
1614FrankrijkNanten
1715OostenrijkWenen
1816Verenigd KoninkrijkLonden
1917DuitslandHamburg
2018FrankrijkCannes
2119FrankrijkLilles
2220PolenWarschau
2321BelgiëGent
2422BelgiëKortrijk
Sheet1
Cell Formulas
RangeFormula
H6:H15H6=UNIQUE(B3:B24)
I6:I11I6=FILTER(C3:C24,B3:B24=F6)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
F6List=Countries#
F7List=Cities#
 
Upvote 0
kevin9999 said:
Good to hear (y)

Is that a question? If you mean you intend naming the top cells in the data validation lists (i.e. the cells with the Unique & Filter formulas) then that's OK - just remember to add a hash # at the end of the names to allow for the spill. Something like this:
Book1
ABCDEFGHI
1NrCountryCity
2-StandaardStandaard
31BelgiëBrussel
42SpanjeBarcelona
53FrankrijkParijsUnique CountriesCities based on choice
64NederlandAmsterdamDuitslandBelgiëFrankfurt
75DuitslandFrankfurtDüsseldorfSpanjeHannover
86DuitslandHannoverFrankrijkDüsseldorf
97DuitslandDüsseldorfNederlandMünchen
108ItaliëMilaanDuitslandBerlijn
119DuitslandMünchenItaliëHamburg
1210FrankrijkLyonUAE
1311DuitslandBerlijnOostenrijk
1412UAEDubaiVerenigd Koninkrijk
1513SpanjeMadridPolen
1614FrankrijkNanten
1715OostenrijkWenen
1816Verenigd KoninkrijkLonden
1917DuitslandHamburg
2018FrankrijkCannes
2119FrankrijkLilles
2220PolenWarschau
2321BelgiëGent
2422BelgiëKortrijk
Sheet1
Cell Formulas
RangeFormula
H6:H15H6=UNIQUE(B3:B24)
I6:I11I6=FILTER(C3:C24,B3:B24=F6)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
F6List=Countries#
F7List=Cities#
Click to expand...
It wasn't a question but yeah you say it correct :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Dropdown dependent dropdown based upon multiple selections.
Replies
0
Views
510
jcschafer209
J
J
  • Question Question
Dropdown selection that returns a different value based on selection.
Replies
5
Views
399
etaf
etaf
A
  • Solved
VBA to copy a template from one sheet to another based on dropdown
2
Replies
10
Views
675
altizerc2196
A
T
  • Question Question
Pull data from every 5th column based on dropdown selection
Replies
4
Views
245
Twinky
T
ChrisMac1
  • Solved
VBA - Change Data Validation drop down value based on a value
2
Replies
17
Views
449
mumps
mumps

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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
Back
Top