Taking The (USA, France...etc) cascading combo boxes further

[KentKHI]

I would like to see if any one can tell me how to set this up to work so it will automatically display the cities population. I tried to use VLOOKUP, but I didn't know what to put in for "table array" value, because it is dependant on what city is in the C column. I am not sure if there is some way to use the indirect formula in that column or what?

Here is what I'm trying to do:

undotest.xls
	A	B	C	D	E	F	G	H
2
3	Country		City		Pop.
4	USA		Pittsburgh
5
6
7			Database
8			countries		cities		population
9			USA		Paris		1234
10			France		Nice		4321
11					Toulon		1212
12
13
14					Pittsburgh		543
15					LA		2345
16					Boston		5432
17
18
Sheet2

Thanks,

Kent

 

[Von Pookie]

"table array" would be the area where your table of information is. In your sample data above, it would be C8:G16--but delete the blank row from the 'database' area.

Edit: perhaps the explanations in this thread may help?
http://www.mrexcel.com/board2/viewtopic.php?t=110428

 

[Aladin Akyurek]

Re: Taking The (USA, France...etc) cascading combo boxes fur

Change the definition of FRANCE to:

Sheet2!$E$9:$G$11

That of USA to:

Sheet2!$E$14:$G$16

Use in C4, the data validation cell:

=INDEX(INDIRECT(A4),0,1)

instead of:

=INDIRECT(A4)

In G4 enter in order to retrieve the population of the city, selected in C4...

=VLOOKUP(C4,INDIRECT(A4),3,0)

 

[Aladin Akyurek]

"table array" would be the area where your table of information is. In your sample data above, it would be C8:G16--but delete the blank row from the 'database' area.

Edit: perhaps the explanations in this thread may help?
http://www.mrexcel.com/board2/viewtopic.php?t=110428

That could give trouble with:

CANADA --> London vs ENGLAND --> London

 

[KentKHI]

Re: Taking The (USA, France...etc) cascading combo boxes fur

Aladin,

I used your first idea and it works great! I haven't tried the table array idea so I'm not sure how that works yet.

Its amazing what excel can do,

Kent