COMBO BOX Drop List Function

andy-s

Board Regular
Joined
Mar 22, 2002
Messages
51
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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
 
Upvote 0
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).<HTML><HEAD><meta http-equiv="Content-Type" content="text/html; charset=windows-1252"></HEAD><BODY><Table border><TR VALIGN="bottom"><TD ALIGN="left"><FONT FACE="Arial">
Cat</FONT></TD><TD ALIGN="left"><FONT FACE="Arial">
Cat1</FONT></TD><TD ALIGN="left"><FONT FACE="Arial">
Duration</FONT></TD><TD ALIGN="left"><FONT FACE="Arial">
Cat2</FONT></TD><TD ALIGN="left"><FONT FACE="Arial">
Duration</FONT></TD><TD ALIGN="left"><FONT FACE="Arial">
Cat3</FONT></TD><TD ALIGN="left"><FONT FACE="Arial">
Duration</FONT></TD></TR><TR VALIGN="bottom"><TD ALIGN="left"><FONT FACE="Arial">
Cat1</FONT></TD><TD ALIGN="left"><FONT FACE="Arial">
internet</FONT></TD><TD ALIGN="right"><FONT FACE="Arial">
6</FONT></TD><TD ALIGN="left"><FONT FACE="Arial">
word</FONT></TD><TD ALIGN="right"><FONT FACE="Arial">
2</FONT></TD><TD ALIGN="left"><FONT FACE="Arial">
vba</FONT></TD><TD ALIGN="right"><FONT FACE="Arial">
5</FONT></TD></TR><TR VALIGN="bottom"><TD ALIGN="left"><FONT FACE="Arial">
Cat2</FONT></TD><TD ALIGN="left"><FONT FACE="Arial">
intranet</FONT></TD><TD ALIGN="right"><FONT FACE="Arial">
10</FONT></TD><TD ALIGN="left"><FONT FACE="Arial">
excel</FONT></TD><TD ALIGN="right"><FONT FACE="Arial">
40</FONT></TD><TD ALIGN="left"><FONT FACE="Arial">
lisp</FONT></TD><TD ALIGN="right"><FONT FACE="Arial">
40</FONT></TD></TR><TR VALIGN="bottom"><TD ALIGN="left"><FONT FACE="Arial">
Cat3</FONT></TD><TD ALIGN="left"><FONT FACE="Arial">
html</FONT></TD><TD ALIGN="right"><FONT FACE="Arial">
10</FONT></TD><TD ALIGN="left"><FONT FACE="Arial">
access</FONT></TD><TD ALIGN="right"><FONT FACE="Arial">
40</FONT></TD><TD ALIGN="left"><FONT FACE="Arial">
apl</FONT></TD><TD ALIGN="right"><FONT FACE="Arial">
25</FONT></TD></TR><TR VALIGN="bottom"><TD ALIGN="right"><FONT FACE="Arial">
 </FONT></TD><TD ALIGN="left"><FONT FACE="Arial">
e-commerce</FONT></TD><TD ALIGN="right"><FONT FACE="Arial">
25</FONT></TD><TD ALIGN="right"><FONT FACE="Arial">
 </FONT></TD><TD ALIGN="right"><FONT FACE="Arial">
 </FONT></TD><TD ALIGN="right"><FONT FACE="Arial">
 </FONT></TD><TD ALIGN="right"><FONT FACE="Arial">
 </FONT></TD></TR></Table><FONT SIZE=-1><FONT SIZE=+0></BODY></HTML>

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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top