I am tryin to have 2 in-cell dropdown lists in an excel worksheet.
The list displayed in the 2nd dropdown is dependent on the value selected in the 1st one. Also, there should be the option for user input other than the listed items.
The first dropdown is working fine, and I have clumsily linked the second list to the 1st one by means of a macro triggered by a flag value stored in another cell. But Im sure there is another better way to do this either in Excel or VB. Can anyone help me? Thanks

Hello,

I have recently produced somthing identical using 2 control combo boxs that are populated from data held in a database, one reliant on the other. Can you tell me where you source of the lists is originating from?

It also sounds like you are using the "Forms" rather than "Control toolbox" version of the ComboBox (Drop down box). Can you confirm?

Cheers

Matt

hi,

IF i understand correctly, you are trying to make 2 cells with in-cell dropdown menu's. Here is what i would do if the 2nd is to be dependent on the first cell

You have cells which contain the values of the dropdown menu. Some for the first dropdown, some for the second.
What you could do is the following:

Two data cells for first in-cell dropdown: A1 and A2
Two data cells for second in-cell dropdown: B1 and B2, which contain formulas

Cell which contains the first in-cell dropdown: C1
Cell which contains the second in-cell dropdown: C2

To make it clear i will create an example
A1 = fruits
A2 = vegetables

                                   IF(criteria, true, false)
put a formula in B1, for example: =IF(C1=A1, "apple", IF(C1=A2, "beans")
put a formula in B2, for example: =IF(C1=A1,"banana", IF(C1=A2, "union")
'this example shows that B1 and B2 change if you select a different value in the cell where you have your dropdown menu.
If B1 and B2 change, your dropdown menu in C2 (which is the cell with 2nd dropdown), so you have a dependent set!

If this is not clear enough, please send me your email address and i will make an example in a worksheet.

Good luck

hey max

what you told me was awesome...I tried it out and it worked flawlessly. But heres the problem... my first dropdown list would have about 35 items, and the dependent lists have no. of items ranging from 1 to 265. So fitting all of that data into conditional statements is cumbersome and inefficient, if not near impossible. Also, doesnt Excel limit nested IF statements to 7 in number? In that case this would'nt work. Any ideas to take this one step higher? Appreciate the advice though, it was really helpful. Thanks

wassup mj

my data is being sourced from coloumns on another worksheet on an excel spreadsheet. I am infact not using the control toolbox, rather am using the data validation option to create dropdown lists.

hi contagiouss blue

Im glad to hear that i at least was clear about my idea. So for 35 items ranging to 265 items, I reckon its rather nasty to create those gigantic if-blocks.
Still, you could use VBA to create some code which will make the dependency.
Unfortunately, Im not able to give you further assistance at this point because I dont know how your dependency works, so a little bit more detail about the cells etc would be useful

You are free to email me if it is personal information or a worksheet so i can have a better view of your plan.

-Max

Considering your position, try the following solution...

In sheet1 of you workbook create a list of data similar to the following
(Row 1 is the header for the row)
Box1 Box2
Box1Item1 Box2Item1
Box1Item1 Box2Item2
Box1Item1 Box2Item2
Box1Item1 Box2Item3
Box1Item2 Box2Item3
Box1Item2 Box2Item4
Box1Item2 Box2Item5

Each unique item in column A is to be displayed in the combobox1
Each unique item in column B is to be displayed in the combobox2 but only if ColA is equal to Combobox1

Display you tool bar "Control Toolbox". Add a button from this toolbar to the worksheet.

Now add two comboboxes from this toolbar into the sheet.

With the design view icon switched on (looks like a set square and ruler) double click the button on the worksheet. This creates the following code.

Private Sub CommandButton1_Click()
End sub

Add the following code to this procedure...
'------------------------------------------------------------
Private Sub CommandButton1_Click()
Dim myLastRow As Integer

Application.ScreenUpdating = False

Range("A2").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

ComboBox1.Clear
ComboBox2.Clear
myLastRow = Range("a65000").End(xlUp).Row
Range("A2").Select

Do Until ActiveCell.Row > myLastRow
    If ActiveCell.Value <> ActiveCell.Offset(-1, 0).Value Then
        ComboBox1.AddItem ActiveCell.Value
    End If
    ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = False

End Sub

Private Sub ComboBox1_Change()
Dim myLastRow As Integer

'Application.ScreenUpdating = False

Range("A2").Select
Selection.Sort Key1:=Range("A2"), Key2:=Range("B2"), Order1:=xlAscending, Order2:=xlAscending, Header:=xlYes
ComboBox2.Clear
myLastRow = Range("a65000").End(xlUp).Row
Range("A2").Select
Do Until ActiveCell.Value = ComboBox1.Value
    ActiveCell.Offset(1, 0).Select
Loop

Do Until ActiveCell.Value <> ComboBox1.Value
    If ActiveCell.Offset(0, 1).Value <> ActiveCell.Offset(-1, 1).Value _
        Or ActiveCell.Offset(0, 0).Value <> ActiveCell.Offset(-1, 0).Value Then
        ComboBox2.AddItem ActiveCell.Offset(0, 1).Value
    End If
    ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = False


End Sub



'-------------------------------------------------------------------

Go back to your worksheet save the file and click on the button to refresh the data in combobox1
make your selection in cb1 and only the desired data appears in cb2.

See how this goes.

Cheers

Matt