Help required with dual combo lookup

StaffsLebowski

New Member
Joined
Nov 18, 2009
Messages
22
Can anybody help with what seems like a simple problem?

I have two columns:

1 - Course Title (Has a combo box that references the range shown below)

2 - Course Reference

When a User selects a value from the Course Title combo box, the Course Reference (using the VLookup shown below) is automatically populated.

=IF(ISBLANK(C2),"",IF(LEN(VLOOKUP(C2,CourseList!$A$1:$B$65535,2,FALSE))>0,VLOOKUP(C2,CourseList!$A$1:$B$65535,2,FALSE),""))

What I'm trying to achieve, is to have two combo boxes. This will allow a User to select either a Course Title or a Course Refence and automatically populate the other field. This seemed quite strange forward at first but I keep getting a circular reference problem that I don't know how to get round.

Any solutions greatly appreciated.

Many thanks,

Staffs Lebowski

COURSE RANGE
----------------------------
CourseTitle: CourseRef:
----------------------------
MyCourse01 1
MyCourse02 2
MyCourse03 3
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Where are your comboboxes from? If they are from Data Validation you won't be able to use formulas - they will be overwritten when the user makes a selection.
 
Upvote 0
Hi Andrew,

Thank you for your quick response. I'm primarily an Access Developer and somewhat 'challenged' in the world of Excel challenges!

I've inherited this problem and trying to find a solution. You're right, the Course Title combo box is from Data Validation. I was hoping to add a Course Reference combo box from Data Validation so the User could select from either combo box.

If this method is not possible, can you suggest another avenue to explore?

Once again, many thanks,

Warren.
 
Upvote 0
First of all name your 2 lists CourseTitles and CourseRefs using Insert|Name|Define and assign those 2 names to your Data Validation cells (using the Allow box). Then right click the tab of the sheet that contains your Data Vaildation and choose View Code. Paste this code into the window on the right:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WF As WorksheetFunction
    Dim CourseRefs As Range
    Dim CourseTitles As Range
    Application.EnableEvents = False
    Set WF = WorksheetFunction
    Set CourseRefs = ThisWorkbook.Names("CourseRefs").RefersToRange
    Set CourseTitles = ThisWorkbook.Names("CourseTitles").RefersToRange
    With Target
        If .Address = "$C$2" Then
            If .Value = "" Then
                Range("D2").Value = ""
            Else
                Range("D2").Value = WF.Index(CourseRefs, WF.Match(.Value, CourseTitles, False))
            End If
        ElseIf .Address = "$D$2" Then
            If .Value = "" Then
                Range("C2").Value = ""
            Else
                Range("C2").Value = WF.Index(CourseTitles, WF.Match(.Value, CourseRefs, False))
            End If
        End If
    End With
    Application.EnableEvents = True
End Sub

The code assumes that your Data Validation is in C2 and D2 - adjust to suit. Close the Visual Basic Editor and try it out by selecting from one of the dropdowns.
 
Upvote 0
Hi Andrew,

Thank you for the time you have put into this. I was expecting to be pointed in a direction, not provided with a solution so this was a pleasant and much appreciated suprise!

I've followed your instruction to the letter, however, I can't get anything to work. I put a breakpoint on the code to see what was happening but it appears as if the 'Worksheet_Change' event is not even firing.

Macro security is set to 'Low'. Any suggestions what I'm doing wrong?

Kind regards,

Warren.
 
Upvote 0
You did put the code in the module for the worksheet (eg Sheet1) didn't you? It won't work in a general module like Module1. Also what version of Excel do you have? maybe try typing this in the Immediate window in the VBE:

Application.EnableEvents = True

and pressing Enter.
 
Upvote 0
Hi Andrew,

To answer your questions first:

"You did put the code in the module for the worksheet (eg Sheet1) didn't you?" - Yes.

"Also what version of Excel do you have?" - 2003, SP3

I have now received the following error:

Runtime Error 1004
Unable to get the Match property of the WorksheetFunction class

This error appearead on line:

Range("D2").Value = WF.Index(CourseRefs, WF.Match(.Value, CourseTitles, False))

Investigation revealed the function...

WF.Match(.Value, CourseTitles, False)

...is where it failed. NB: The .Value parameter was a valid CourseRef

Kind regards,

Warren
 
Upvote 0
Hi Andrew,

I have this working now, perfectly. Thank you!

Can you tell me, how would you implement it for multiple rows? So a User could enter another course on another row?

Regards,

Warren.
 
Upvote 0
I have amended the code so that it works with data validation pairs in the range C2:D11:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WF As WorksheetFunction
    Dim CourseRefs As Range
    Dim CourseTitles As Range
    If Target.Count > 1 Then Exit Sub
    If Application.Intersect(Target, Range("C2:D11")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Set WF = WorksheetFunction
    Set CourseRefs = ThisWorkbook.Names("CourseRefs").RefersToRange
    Set CourseTitles = ThisWorkbook.Names("CourseTitles").RefersToRange
    With Target
        If .Column = 3 Then
            If .Value = "" Then
                .Offset(, 1).Value = ""
            Else
                .Offset(, 1).Value = WF.Index(CourseRefs, WF.Match(.Value, CourseTitles, False))
            End If
        ElseIf .Column = 4 Then
            If .Value = "" Then
                .Offset(, -1).Value = ""
            Else
                .Offset(, -1).Value = WF.Index(CourseTitles, WF.Match(.Value, CourseRefs, False))
            End If
        End If
    End With
    Application.EnableEvents = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

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