3 linked drop down lists referenced off a table(s)??

[DMJ62]

Hi everyone,
I've been trying very hard to tackle this on my own, but this is taking a toll my nerves! I’ve been searching everywhere for answers, but feel like either I’m struggling to find the right answer. I don’t see an option to upload my file so I’ll try to rebuild it.
So here's what I’m trying to do:
1st I was able to get the 3 tier dropdown list to work between columns A-I. B2:E4 are the list dropdown that reference data in columns G-I. The list drop downs are what I want, but I don’t really like the organization of the data in G-I. I’m hoping for tables or one table. I think by using a table format will be cleaner and allow greater dynamics.

So in column L-O I made three tables to resemble the data in columns G-I. Column Q-U can be used as the list dropdown for the tables. I’m really confused on how to have the list validations link to the tables or if I even have this table with the data organized properly.

Can anyone teach me or show me an example of how to do this on the attached spreadsheet?
(first time using the MrExcel HTML maker. lol i hope

Excel 2010

	A	B	C	D	E	F	G	H	I
1		Country	City	Street	area code		Netherlands	Britain	Germany
2	David	Britain	Manchester	ManchesterStreet1	103		Amsterdam	London	Bonn
3	Linda						Rotterdam	Canterbury	Berlin
4	Peter	Netherlands	Eindhoven	EindhovenStreet1	102		Eindhoven	Manchester	Munich
5
6							Amsterdam	London	Bonn
7							AmsterdamStreet1	LondonStreet1	BonnStreet1
8							AmsterdamStreet2	LondonStreet2	BonnStreet2
9
10							Rotterdam	Canterbury	Berlin
11							RotterdamStreet1	CanterburyStreet1	BerlinStreet1
12							RotterdamStreet2	CanterburyStreet2	BerlinStreet2
13
14							Eindhoven	Manchester	Munich
15							EindhovenStreet1	ManchesterStreet1	MunichStreet1
16							EindhovenStreet2	ManchesterStreet2	MunichStreet2
17
18							AmsterdamStreet1	LondonStreet1	BonnStreet1
19							407	321	852
20							904	565	456
21							305	568	951
22							AmsterdamStreet2	LondonStreet2	BonnStreet2
23							222	753	984
24							333	862	789
25							444	842	432
26							RotterdamStreet1	CanterburyStreet1	BerlinStreet1
27							111	805	501
28							666	905	502
29							RotterdamStreet2	CanterburyStreet2	BerlinStreet2
30							777	702	706
31							999	705	708
32							EindhovenStreet1	ManchesterStreet1	MunichStreet1
33							101	103	105
34							102	104	106
35							EindhovenStreet2	ManchesterStreet2	MunichStreet2
36							508	900	100
37							509	800	99
38							11	97	98

<tbody>
</tbody>

Sheet1​

Above is the table that works with linked dropdown list to the data on the right. Below are the tables that are (I think dynamic) but like I described I don’t know how to reference the tables so they’re dependent on the list selection. I wish I could upload my file. L I could only find instruction on converting my information into html.

Excel 2010

	L	M	N	O	P	Q	R	S	T	U
1	Netherlands Country Table						Country	City	Street	area code
2	City	Address	Area Code	Column1		David
3	Amsterdam	AmsterdamStreet1	407	Amsterdam, AmsterdamStreet1, 407		Linda
4	Amsterdam	AmsterdamStreet1	904	Amsterdam, AmsterdamStreet1, 904		Peter
5	Amsterdam	AmsterdamStreet1	305	Amsterdam, AmsterdamStreet1, 305
6	Amsterdam	AmsterdamStreet2	222	Amsterdam, AmsterdamStreet2, 222
7	Amsterdam	AmsterdamStreet2	333	Amsterdam, AmsterdamStreet2, 333
8	Amsterdam	AmsterdamStreet2	444	Amsterdam, AmsterdamStreet2, 444
9	Rotterdam	RotterdamStreet1	111	Rotterdam, RotterdamStreet1, 111
10	Rotterdam	RotterdamStreet1	666	Rotterdam, RotterdamStreet1, 666
11	Rotterdam	RotterdamStreet2	777	Rotterdam, RotterdamStreet2, 777
12	Rotterdam	RotterdamStreet2	999	Rotterdam, RotterdamStreet2, 999
13	Eindhoven	EindhovenStreet1	101	Eindhoven, EindhovenStreet1, 101
14	Eindhoven	EindhovenStreet1	102	Eindhoven, EindhovenStreet1, 102
15	Eindhoven	EindhovenStreet2	508	Eindhoven, EindhovenStreet2, 508
16	Eindhoven	EindhovenStreet2	509	Eindhoven, EindhovenStreet2, 509
17	Eindhoven	EindhovenStreet2	11	Eindhoven, EindhovenStreet2, 11
18
19	Britain Country Table
20	City	Address	Area Code	Column1
21	London	LondonStreet1	321	London, LondonStreet1, 321
22	London	LondonStreet1	565	London, LondonStreet1, 565
23	London	LondonStreet1	568	London, LondonStreet1, 568
24	London	LondonStreet2	753	London, LondonStreet2, 753
25	London	LondonStreet2	862	London, LondonStreet2, 862
26	London	LondonStreet2	842	London, LondonStreet2, 842
27	Canterbury	CanterburyStreet1	805	Canterbury, CanterburyStreet1, 805
28	Canterbury	CanterburyStreet1	905	Canterbury, CanterburyStreet1, 905
29	Canterbury	CanterburyStreet2	702	Canterbury, CanterburyStreet2, 702
30	Canterbury	CanterburyStreet2	705	Canterbury, CanterburyStreet2, 705
31	Manchester	ManchesterStreet1	103	Manchester, ManchesterStreet1, 103
32	Manchester	ManchesterStreet1	104	Manchester, ManchesterStreet1, 104
33	Manchester	ManchesterStreet2	900	Manchester, ManchesterStreet2, 900
34	Manchester	ManchesterStreet2	800	Manchester, ManchesterStreet2, 800
35	Manchester	ManchesterStreet2	97	Manchester, ManchesterStreet2, 97
36
37	Germany Country Table
38	City	Address	Area Code	Column1
39	Bonn	BonnStreet1	852	Bonn, BonnStreet1, 852
40	Bonn	BonnStreet1	456	Bonn, BonnStreet1, 456
41	Bonn	BonnStreet1	951	Bonn, BonnStreet1, 951
42	Bonn	BonnStreet2	984	Bonn, BonnStreet2, 984
43	Bonn	BonnStreet2	789	Bonn, BonnStreet2, 789
44	Bonn	BonnStreet2	432	Bonn, BonnStreet2, 432
45	Berlin	BerlinStreet1	501	Berlin, BerlinStreet1, 501
46	Berlin	BerlinStreet1	502	Berlin, BerlinStreet1, 502
47	Berlin	BerlinStreet2	706	Berlin, BerlinStreet2, 706
48	Berlin	BerlinStreet2	708	Berlin, BerlinStreet2, 708
49	Berlin	BerlinStreet2	105	Berlin, BerlinStreet2, 105
50	Munich	MunichStreet1	106	Munich, MunichStreet1, 106
51	Munich	MunichStreet1	100	Munich, MunichStreet1, 100
52	Munich	MunichStreet2	99	Munich, MunichStreet2, 99
53	Munich	MunichStreet2	98	Munich, MunichStreet2, 98

<tbody>
</tbody>

Sheet1
​

this is my first post on this site, so please give me friendly guidance

 

[DMJ62]

I found an example that I think is similar to what I’m looking for. However, the Lists tab seems like it could become very extensive? is there a better way to reference/organize this list? Excel Data Validation -- Dependent Lists With INDEX (sorry, hope I didn't violate any rules by posting an example reference from another site.)

 

[DMJ62]

i think i found what i was looking for (finally)!
Excel Magic Trick 781: Three Way Lookup: INDEX and MATCH and Concatenated Ranges & Cells - YouTube from ExcellsFUN