Populating Excel user form with value from second column of multi-column combobox dropdown

In Formulas Tab->Name Manager, select the TaskLIst named range and change the Refers To formula to

=OFFSET(Tasks!$B$2,0,0,MAX(COUNTA(Tasks!$B:$B),COUNTA(Tasks!$C:$C)),3)

if you want to see Task, PA_Unit and Date, or to

=OFFSET(Tasks!$B$2,0,0,MAX(COUNTA(Tasks!$B:$B),COUNTA(Tasks!$C:$C)),2)

if you want to see Task and PA_Unit.

Thanks, Martin, for your quick response. I tried it, and noticed that I don't see the ID number any longer in the dropdown list. Sorry, I should have mentioned that I'm looking for a solution, if possible, that retains the same columns in the original dropdown, and that VBA code can somehow select the value from the second column.

Cheers,
Andrea

I'm confused since if you go back to the original formula it seems to be doing that already.

Yes, it appears that the spreadsheet is being populated correctly. It’s the user form display that is my problem. When the user selects the dropdown menu for Task, the user form fields for both Task and ID are both populated with ID. On the user form, I would like, if possible, for the Task field to be populated with the value in the Task column (column 2) instead of the value from the ID column (column 1).

Hope that makes sense...?

Cheers,
Andrea

When the user selects the dropdown menu for Task, the user form fields for both Task and ID are both populated with ID.

Are you seeing the same thing I am? It looks to me like it's already doing what you want. If it's not then please tell me what should appear, for example, in the first row of the combobox display.

Hi Martin,

The user form view is the issue, not the spreadsheets, as currently two user form fields have the same value (ID).  I'm hoping that there is VBA code that, while maintaining the current 3-column dropdown for the user, can populate the Task field in the user form from column 2 (Task) instead of column 1 (ID). I already have the ID field below, which is auto-populated based on the user's selection in the Task combobox:

Sorry that it took me this long to understand but I think I do now. I have to go out but I'll be back in a couple of hours.

Try this.
29072884.xlsm

Hi Martin,

Good news, and a couple of issues...

The good news is that the user form is now populating correctly - thank you! The 'TaskList' range, however, appeared to have been changed from referencing the 'Tasks' sheet (which was hidden, and is a unique list of the tasks), to the TrackingSheet. As a result, the dropdown was showing duplicate tasks and was out of order, etc. I changed the 'TaskList' range back to the 'Tasks' sheet, and that reverted the dropdown list to an ordered list of unique tasks, which was good. However, I noticed that when I now add new records, Column B (the task description) of the TrackingSheet remains blank. All the other columns are populated correctly.

So the upshot is:
The user form fields are being populated correctly
The TrackingSheet is being populated correctly with the exception of Column B, which is blank

Thanks for your efforts on this, and I'm hoping that the TrackingSheet issue can be resolved...

Cheers,
Andrea

I didn't realize that the Task sheet existed and that's why I changed the TaskList named range. I'll be back.

Thanks so much, Martin, this is perfect! And I apologize for neglecting to mention the hidden Task sheet - out of sight, out of mind. :-(

I like the way you enhanced the Task combobox dropdown by adding a heading row. Thanks for going the extra mile!

Cheers,
Andrea

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017

Thanks, Martin, I’ll follow up...

Cheers,
Andrea