Creating dynamic selection boxes

[mvpdep]

Good afternoon could someone please advise me how I am able to fill two drop down selection boxes. For example if I have on a separate spread sheet column A1-A3 the following:
James
John
Sue
And in column B1-3 I have
Toronto
Concord

I would like on a separate sheet to have the first drop down show the city (TO,CON) and based on what is selected the second drop down only shows a selection of names linked to that city.

For example if TOronto is selected second box would only show James and Sue.

Thank you.

 

[openthis]

Is this what you want?

Private Sub ComboBox1_Change()
theIndex = ComboBox1.ListIndex
If theIndex = 0 Then
ComboBox2.ListFillRange = "B1:B2"
ElseIf theIndex = 1 Then
ComboBox2.ListFillRange = "C1:C2"
End If
End Sub

 

[SkipVought]

Hi,

This question has been answered many times in this forum and in forum68.

I, personally like using the OFFSET worksheet function to define a dynamic range based on the first selection.

1) the SECOND list (2 columns) must be sorted by the lookup value, in column 1 with the Second list values in column 2 in A2...
[tt]
Blue Blueberry
Blue Concord Grape
Red Cherry
Red Raspberry
Yellow Lemon
Yellow Banana
[/tt]
the second list range
[tt]
=offset(A1,Match(SelectedValue,A2:A7,0),1,CountA(A2:A7,SelectedValue),1)
[/tt]



Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[GlennUK]

Hi Skip,

you'd be better of having absolute references in your offset formula .... and also COUNTIF instead of COUNTA

Anyway, Debra Dalgleish covers dependant drop-downs extensively on her website:

http://www.contextures.com/xlDataVal13.html

http://www.contextures.com/xlDataVal02.html

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

Glenn,

Yup!


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}