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