How to Proceed... Populate Dynamic List based on cell value

[programsam]

Hi gang,

I'm trying to find the best way to approach this issue and I have no idea where to start and I've bounced around from idea to idea and cannot put the pieces together.

Here's what I want to do.

I've got a file that when opened, automatically identifies the name of the user that is logged in and places that name in a hidden worksheet called "Users" in cell F4.

On a separate worksheet entitled "Partners", I've got a table of data that updates every time the file is opened. One column of data (Column A) contains the names of the different users that could potentially log in and the corresponding column (Column B) contains partners that will work alongside the user. What I want is for the system to recognize whom is logged in, locate that name in Column A and return ALL of the potential partners that correspond that name in Column B in to a list on a worksheet entitled "Dashboard". As an example, the data would look something like this.

<table>
<tr><td>Users:</td><td>Partners:</td></tr>
<tr><td>Tom Jones</td><td>A & L LLC</td></tr>
<tr><td>Tom Jones</td><td>RapidRock Mining</td></tr>
<tr><td>Cindy Marek</td><td>Gypsum Craftwerks</td></tr>
<tr><td>Tom Jones</td><td>Milestone Group</td></tr>
<tr><td>Alan Jameson</td><td>Harpoon Services</td></tr>
</table>

So, the system will automatically recognize the name of the User, pull data from the table that corresponds and display it in a list on another worksheet.

I've racked my brain on the best approach be it VBA, Data Validated List (not sure how it would work) or some mixture of the two.

Your assistance is much appreciated.

 

[Tinbendr]

pull data from the table that corresponds and display it in a list on another worksheet.

You didn't indicate what 'kind' of list to create.

Here's a link to the sample file.

In the ThisWorkbook code page.

Private Sub Workbook_Open()
GetUserClients
End Sub

Add A Module.

Sub GetUserClients()
Dim WS As Worksheet
Dim LastRow As Long
Dim DestLR As Long
Dim A As Long
Dim C As Range, FirstAddress As String
Dim UN As String

UN = "Tom Jones" 'Application.UserName

Set WS = Worksheets("Dashboard")
With WS
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    .Range("A2:A" & LastRow).Delete
End With
DestLR = 2

Set WS = Worksheets("Users")

With WS
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    With .Range("a1:a" & LastRow)
        Set C = .Find(UN, LookIn:=xlValues)
        If Not C Is Nothing Then
            FirstAddress = C.Address
            Do
                Worksheets("Dashboard").Range("A" & DestLR) = C.Offset(0, 1)
                DestLR = DestLR + 1
                Set C = .FindNext(C)
            Loop While Not C Is Nothing And C.Address <> FirstAddress
        End If
    End With
End With
End Sub