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

programsam

Board Regular
Joined
Feb 10, 2016
Messages
77
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. :confused:

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.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Tinbendr

Well-known Member
Joined
Jul 21, 2010
Messages
997
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.
Code:
Private Sub Workbook_Open()
GetUserClients
End Sub
Add A Module.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,230
Messages
5,594,949
Members
413,953
Latest member
Arthur1471

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
Top