cascading combo boxes...???

[Gurps]

is there any way in which I can get the selection from one combo box to influence the second?? In other words, The second combo box is dependant on the first.
I am using Excel XP.

any help would be appreciated.
cheers

 

[Barry Katcher]

Hi, guyz & galz.

I'm VBA challenged (a complete idiot, in fact). Can anyone come up with a Worksheet_SelectionChange or Worksheet_Change code to blank out cell D4 (my dependent validation cell) whenever cell B4 (my primary validation cell) is edited (either the contents deleted or a new choice made).

Thanx.

 

[SIMON123]

Hi

I've setup a worksheet that has a TEAM listing (Teams 1 to 10), which I have used to populate ComboBox1 on a Userform. I have named this as a range called TEAMS.

On a seperate sheet, I have another list which has players registered to each team. Some teams have 10 players, some have 20 players, etc.

This PLAYER listing is a 2 column list, the first column being the team and the second being the Player Name. I have set the whole of this listing to the name PLAYERS.

I want my second Combobox on the Userform to show only the players that are registered againat the team selected in ComboBox1. i.e only show the PLAYER list when the first column is the same as the one in Combobox1.

I've spent ages trying to get this to work. I'm only a VBA baby so this may be a very simple bit of code.

Hope that this makes sense!!

Can anyone help?

Cheers

Simon

 

[Norie]

Simon

Could you start a new thread for this?

 

[mqarep]

I Just Can't Get This to Work

I searched the forums for what I am trying to do, and this example looks perfect, but something is missing - I can't get it to work.

What I have is:

Column 1 =
Expenditure Type
Asset Category
Column 2 =
Admin-All Staff Functions
Admin-Bus Licenses
Admin-Charitable Donations
Admin-Comm Cellular
Column 3=
Building-Rate 4
Building-Rate 5
Building-Rate 8
Building-Rate 10

When "Expenditure Type" is selected in the pseudo combo box I want the 2nd pseudo combo box to contain the data listed in column 2

When "Asset Category is selected, I want the list to contain the data in column 3

How is this done step-by-step?
Thanks.

 

[Aladin Akyurek]

Re: I Just Can't Get This to Work

I searched the forums for what I am trying to do, and this example looks perfect, but something is missing - I can't get it to work.

What I have is:

Column 1 =
Expenditure Type
Asset Category
Column 2 =
Admin-All Staff Functions
Admin-Bus Licenses
Admin-Charitable Donations
Admin-Comm Cellular
Column 3=
Building-Rate 4
Building-Rate 5
Building-Rate 8
Building-Rate 10

When "Expenditure Type" is selected in the pseudo combo box I want the 2nd pseudo combo box to contain the data listed in column 2

When "Asset Category is selected, I want the list to contain the data in column 3

How is this done step-by-step?
Thanks.

Here is how it is done...

Book3
	A	B	C	D
1	MainList	SubList-1	SubList-2
2	Expenditure Type	Admin-All Staff Functions	Building-Rate 4
3	Asset Category	Admin-Bus Licenses	Building-Rate 5
4		Admin-Charitable Donations	Building-Rate 8
5		Admin-Comm Cellular	Building-Rate 10
6
7
Sheet1

Select A2:A3 and name it (via the Name Box) MainList

Select B2:B5 and name it (via the Name Box) ExpenditureType. Note the absence of space!

Select C2:C5 and name it (via the Name Box) AssetCategory. Note the absence of space!

Try the following...

Select F2.
Activate Data|Validate.
Choose List for Allow.
Enter the following in the Source box:

=MainList

Click OK.

Select G2.
Activate Data|Validate.
Choose List for Allow.
Enter the following in the Source box:

=INDIRECT(SUBSTITUTE(F2," ",""))

Click OK.

 

[tusharm]

For a solution to dependent lists (or cascading queries) that uses data in relational tables see
Cascading queries
http://www.tushar-mehta.com/excel/newsgroups/cascading_dropdowns/index.html

 

[tazzz4200]

That was alot of help! But is there a way to have some of the information in the first drop-down point to the same information. For example: if the information in the first drop-down menu was: A Segment, B Segment, and Overlay and I want anything with the word Segment in it to point to the same menu but Overlay point to a different one.

I tried to do a Name with *Segment but that obviously didn't work.

 

[Aladin Akyurek]

That was alot of help! But is there a way to have some of the information in the first drop-down point to the same information. For example: if the information in the first drop-down menu was: A Segment, B Segment, and Overlay and I want anything with the word Segment in it to point to the same menu but Overlay point to a different one.

I tried to do a Name with *Segment but that obviously didn't work.

See:

http://www.mrexcel.com/board2/viewtopic.php?t=247568

 

[RichardMGreen]

Does anyone know if INDIRECT can be used with list boxes rather than comboboxes?

 

[wereman]

Dear Eric
I have the same problem as you quote here. Were you able to solve it?

Wereman