Show column heading and corresponding value based on dropdown selection

[Daph1990]

Hi,

Been trying to figure this out for days. This is just a copy of the real file. The first 3 columns (area, category and sub-category) are chosen by the user via a combobox on userform - this part works perfectly, it is dynamic and selects the category and subcategory based on area if they exist.

The part I am stuck with is that based on those 3 selections (or 2 if looking at Area: IT Systems and Qualifications), I would like for a persons name to be displayed (preferably in a listbox as part of a userform)along with their level of competency i.e confident or very confident. So for example if IT Systems is selected from Area and Curtis Fitch from category, I want code that will look across that row and show the name of the person who has that skill and their level of skill in just that area.

The file I have attached does something similar but for the purpose of the example instead of having a combobox selection I've made it an inputbox (I'll adapt this to suit my real version), so it searches the whole column based on the persons name typed in so basically it works in the opposite order and I'm having a hard time adapting it to the way I require.

Any help will be greatly appreciated.

Cheers

[mdmackillop]

This uses the row number from the textbox. I assume you will provide this from your code

[Daph1990]

You are literally a genius, I've been stuck on this for over a week! Thank you soooo much . One last question if I may, when I select a new line number and click the command button it doesn't delete the previous results, what would I add to the code to get it to do this.

Thanks again!

[mdmackillop]

Before repopulating the listbox

 ListBox1.Clear

[Daph1990]

Of course, had it in the wrong place! LAST question, I promise....when there is nothing found I would like a msgbox. I've added this code after set rng but it bring back an error...any ideas? Really appreciate your help so far

    If Rng Is Nothing Then    MsgBox "Nothing Found"
    Exit Sub
    End If

[mdmackillop]

Rng will error if there are no values so

On Error Resume Next
    Set Rng = Cells(r, 4).Resize(, Col).SpecialCells(xlCellTypeConstants)
    If Err Then
    MsgBox "Nothing found"
    Exit Sub
End If

[Daph1990]

Amazing, thank you!