Excel Combo Box If/Then Code 1

[sazi22]

I know this must be simple, but I have spent the morning searching posts and trying on my own...

I have cell D10 with data validation that allows the user to pick from "A, B, or C."

If they select "A," I want "list A" to appear in either cell E10 or a combo box, "B" and "list B" etc.

Here's what I was using for the combo box code...

Private Sub ComboBox1_Change()
If Range("D10").Text = "A" Then
ComboBox1 = Range("List A").Text
Else
If Range("D10").Text = "B" Then
ComboBox1 = Range("List B").Text
Else
If Range("D10").Text = "C" Then
ComboBox1 = Range("List C").Text
End If
End If
End If

End Sub

Would it be easier to code in the cell?

Thanks for any help you can provide!

 

[SkipVought]

Here's a way to do it WITHOUT ANY CODE.

1. I set up a second table like this...
[tt]
Col1 Col2
a aa1
a aa2
b bb1
b bb2
b bb3
c cc1
c cc2
[/tt]
then I used Insert/Name/Createnames in top row to define the ranges for tese two columns of data.

Then in Insert/Name/Define...

I defined a range named VarList with this reference
[tt]
=OFFSET(Sheet3!$B$1,MATCH(Sheet3!$G$2,Col1,0),0,COUNTIF(Col1,Sheet3!$G$2),1)
[/tt]
where G2 is the cell referenc containing the result from the FIRST selection (ie a, b, or c)

Then, in the Second Dropdown List Reference
[tt]
=VarList
[/tt]
VOLA!


Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[sazi22]

Thanks Skip!

What does B1 reference?

 

[SkipVought]

I assumed the table begins in A1. So B1 is Col2

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[sazi22]

It's working, but I am only getting 1 item in the drop down list...

Here's my VarList:

=OFFSET(Sheet1!$O$1,MATCH(Sheet1!$D$10,Column A,0),0,COUNTIF(Column A,Sheet1!$D$10),1)

 

[SkipVought]

Here's how to debug this range formula...
[tt]
=OFFSET(Sheet1!$O$1,MATCH(Sheet1!$D$10,Column A,0),0,COUNTIF(Column A,Sheet1!$D$10),1)
[/tt]
Sheet1!$O$1 This is the HEADING for the COLUMN that has your range

MATCH(Sheet1!$D$10,Column A,0) This is the ROW OFFSET of the START VALUE. For instance in my example, if Sheet1!$D$10 contained c, then MATCH would return 6.

COUNTIF(Column A,Sheet1!$D$10) This is the NUMBER OF ROWS in the selected range. For instance in my example, if Sheet1!$D$10 contained c, then COUNTIF would return 2.

Your Formula has a fundamental problem, however.
[tt]
=OFFSET(Sheet1!$O$1,MATCH(Sheet1!$D$10,Column A,0),0,COUNTIF(Column A,Sheet1!$D$10),1)
[/tt]
(Column A is not a valid range name. I suggest that you "build" the OFFSET formula using point 'n' click at the appropriate points in the construction. The only TYPING that you need do is the FUNCTION NAMES and punctuation)


Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[sazi22]

Thank you so much!! I've got the list now. The only problem is when I select "c" in the 1st column, the list comes back with "#Value!" for each "a" or "b" item. How can I get it to only show a list for what is selected in the 1st column?

 

[SkipVought]

Please explain to me EXACTLY where everything is in your workbook.

1. Where is the PRIMARY list a, b, c from which you select in the FIRST dropdown?

2. Where is the SECONDARY list -- like the one in my example; col1 & col2?

3. Where do you use the OFFSET formula? Did you assign a Range Name?

4. How are you USING this OFFSET reference?

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[sazi22]

It is all in Sheet1

1. the Primary list is named "Col1" located in column N.
a N2:N11
b N12:N33
c N34:N52

2. the Secondary list is named "Col2" located in column O.

3. I assigned the name "VarList" to the OFFSET formula and use it in column P.

4. I reference "VarList" in the second drop down list, column E.

Does that help? (This is the first time I am using OFFSET.)

 

[SkipVought]

Your PRIMARY list should be a, b, c THREE CELL RANGE -- like A2:A4

Looks to me like your SECONDARY table is N2:O52

So your OFFSET formula would be
[tt]
=OFFSET(Sheet1!$O$1,MATCH(Sheet1!$D$10,Sheet1!$N$1:$N$52,0),0,COUNTIF(Sheet1!$N$1:$N$52,Sheet1!$D$10),1)[/tt]
as long as D10 contains a lookup value (a, b or c)

3. I assigned the name "VarList" to the OFFSET formula and use it in column P.

what is this for? I agree with yor #4 but this has no meaning at all. VarList is a DYNAMIC range that depends on your lookup value (a, b or c). It's only use is in the SECOND DROPDOWN. What a you attempting to accomplish in column P?

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[sazi22]

I cut and pasted the OFFSET formula when I didn't get a list returned.... Does the OFFSET formula go in a cell?

 

[SkipVought]

Does the OFFSET formula go in a cell?

Then in Insert/Name/Define...

I defined a range named VarList with this reference

=OFFSET(Sheet3!$B$1,MATCH(Sheet3!$G$2,Col1,0),0,COUNTIF(Col1,Sheet3!$G$2),1

Insert/Name/Define

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[sazi22]

I defined the range after i put it in the cell... I didn't put it directly into the reference. It works...

Thank you so much for your patience and help!