COMBO BOX Drop List Function

[andy-s]

Excellent,
this was just what I was looking for also.

The main formula syntax is

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

In this case the Table_array is not placed in ascending order, and therefore Range_lookup should be FALSE, and the formula should look like

=VLOOKUP(INDEX(INDIRECT(INDEX(Categories,B4)),E4),INDIRECT(INDEX(Categories,B4)&"List"),2,FALSE)

This works well for me.

andy-s

[edith168]

Dear Mr. Excel,
I got some big problems and really need ur help badly.

I am now developing a list for training course, I need to display 3 fields (1. Course Categories 2. Course Name 3. Duration). Since there are many courses under each category. I would like to use 2 combo box for "Course Categories" & "Course Name" field to list all the information rather than typing it eveytimes.

I got 2 questions:
1. after i selected range & cell link in the combo box, everytime when i select the combo box. It will only display the no. (e.g 1 for selection 1, 2 for selection 2).But what i need is to display the text, how can i do it?

2. How can I interlink the two combo box. like when i choose the category 1, the other combo box will list those courses under category 1? Do I need to write any marco and how to write?

I really appreciate ur help and thank u so much in advance!!!

hope to rec.ur reply soon, many thanks!
edith

[Aladin Akyurek]

On 2002-04-13 00:43, edith168 wrote:
Dear Mr. Excel,
I got some big problems and really need ur help badly.

I am now developing a list for training course, I need to display 3 fields (1. Course Categories 2. Course Name 3. Duration). Since there are many courses under each category. I would like to use 2 combo box for "Course Categories" & "Course Name" field to list all the information rather than typing it eveytimes.

I got 2 questions:
1. after i selected range & cell link in the combo box, everytime when i select the combo box. It will only display the no. (e.g 1 for selection 1, 2 for selection 2).But what i need is to display the text, how can i do it?

2. How can I interlink the two combo box. like when i choose the category 1, the other combo box will list those courses under category 1? Do I need to write any marco and how to write?

I really appreciate ur help and thank u so much in advance!!!

hope to rec.ur reply soon, many thanks!
edith

Edith,

What follows might meet your needs. It might also require some nerve to implement. It's anycase something that is done using dropdown lists created with data validation. The scheme below has the same logic, but uses the comboboxes created with Toolbars|Forms.

Insert an empty worksheet in your workbook, if you already don't have one. Name this worksheet Admin. Create the following data in A1:G5 (I trust it's similar to your own data).


****** http-equiv="Content-Type" content="text/html; charset=windows-1252">

Cat	Cat1	Duration	Cat2	Duration	Cat3	Duration
Cat1	internet	6	word	2	vba	5
Cat2	intranet	10	excel	40	lisp	40
Cat3	html	10	access	40	apl	25
*	e-commerce	25	*	*	*	*

Select A2:A4 , go to the Name Box on the Formula Bar, type Categories, and hit enter.
Select B2:B5, name the selection Cat1 following the foregoing procedure.
Select B2:C5 and name the selection Cat1List.
Select D2:D4 and name the selection Cat2.
Select D2:E4 and name the selection Cat2List.
Select F2:F4 and name the selection Cat3.
Select F2:G4 and name the selection Cat3List.

Activate now another worksheet and name it Main.
Activate the option Toolbars|Forms.
In B4 create a combobox.
Activate Format Control.
Enter as Input range

Categories

and as Cell link

B4.

Activate the option Insert|Name|Define.
Enter Current as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=INDIRECT(INDEX(Categories,Main!$B$4))

Create another combobox in cell E4.
Activate Format Control.
Enter Current as Input range.
Enter E4 as Cell link.

In H4 enter:

=VLOOKUP(INDEX(INDIRECT(INDEX(Categories,B4)),E4),INDIRECT(INDEX(Categories,B4)&"List"),2)

This formula retrieves the course duration that is selected from the second combobox which is a subcategory of the category of course, selected from the first combobox.

Aladin




[ This Message was edited by: Aladin Akyurek on 2002-04-13 15:01 ]

[ This Message was edited by: Aladin Akyurek on 2002-04-13 15:02 ]

[ This Message was edited by: Aladin Akyurek on 2002-04-13 15:03 ]

[edith168]

Dear Aladin,
This is exactly what i need to do! Thank you soooo...much for ur big help. You are such a genius!!
Thank you for sharing your knowledge to others!
Take Care & Have a nice day!
=) edith

[MrExcel]

April 14th

[edith168]

thank you very much for ur previous, it was great. However, there are still another problem and hope u could help me.

When I follow your steps in creating the combox, it works and the two combox able to link together. However, problem comes out when creating several row of these kind of combo box.

(refer to ur prevoious data)

I have created a combobox in next row B5.
Activate Format Control.
Enter as Input range "Categories".
and as cell link B5.

Activate the option Insert/Name/Define.
Entered "Current1" as name in workbook box.
Enter as formula in the Refer to box:

=INDIRECT(INDEX(Categories,Main!$B$5))

Create another combobox in cell E5.
Activate Format Control.
Enter "Current1" as Input range.
Enter E5 as Cell Link.

*****However, an ERROR MESSAGE OF "REFERENCE IS NOT VALID" comes out****

Do u know what is the reason?

Please help me to solve this problem, hope to receive ur reply soon.

[Aladin Akyurek]

On 2002-04-24 22:17, edith168 wrote:
thank you very much for ur previous, it was great. However, there are still another problem and hope u could help me.

When I follow your steps in creating the combox, it works and the two combox able to link together. However, problem comes out when creating several row of these kind of combo box.

(refer to ur prevoious data)

I have created a combobox in next row B5.
Activate Format Control.
Enter as Input range "Categories".
and as cell link B5.

Activate the option Insert/Name/Define.
Entered "Current1" as name in workbook box.
Enter as formula in the Refer to box:

=INDIRECT(INDEX(Categories,Main!$B$5))

Create another combobox in cell E5.
Activate Format Control.
Enter "Current1" as Input range.
Enter E5 as Cell Link.

*****However, an ERROR MESSAGE OF "REFERENCE IS NOT VALID" comes out****

Do u know what is the reason?

Please help me to solve this problem, hope to receive ur reply soon.

In row 5 I created another pair of linked comboboxes, but I couldn't replicate the problem you describe. Re-check format control properties of the comboboxes you created. I can send you a copy of the WB if you want to.

Aladin

[edith168]

Hi! Aladin,
It will be grate if u will able to send me the example. my email address is edith168@hotmail.com
thank you so much!
Edith

[edith168]

Hi! Aladin,
It will be grate if u will able to send me the example. my email address is edith168@hotmail.com
thank you so much!
Edith

On 2002-04-24 23:05, Aladin Akyurek wrote:

In row 5 I created another pair of linked comboboxes, but I couldn't replicate the problem you describe. Re-check format control properties of the comboboxes you created. I can send you a copy of the WB if you want to.

Aladin

[edith168]

Hi! Aladin,
It will be grate if u will able to send me the example. my email address is edith168@hotmail.com
thank you so much!
Edith