Retrieving Data from Multiple Sheets on Matching Criteria

[reberryjr]

I'm in need of some assistance with a task I'm trying to complete. I have a sheet called "Client Summary", where I'm trying to pull in data from several other sheets where the "Client ID" matches on each sheet. Depending upon the sheet I'm hitting, it could be a 1:1 or 1:many search. Here are a couple of examples:

I would expect to see "Tommy" in the Nickname field of the Client Summary sheet, and -5.00 in the Weight Change field, from the Stats sheet. Note that on the Stat sheet, there are multiple rows for the Client ID.
Client Summary Sheet
[TABLE="class: grid, width: 550"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Today[/TD]
[TD]Updated[/TD]
[TD]Status[/TD]
[TD]Client ID[/TD]
[TD]Name[/TD]
[TD]Nickname[/TD]
[TD]Weight Change[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TJ1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Bios Sheet
[TABLE="class: grid, width: 700"]
<colgroup><col span="2"><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Today[/TD]
[TD]Updated[/TD]
[TD]Status[/TD]
[TD]Key[/TD]
[TD]Client ID[/TD]
[TD]First[/TD]
[TD]Last[/TD]
[TD]Suffix[/TD]
[TD]Name[/TD]
[TD]Nickname[/TD]
[/TR]
[TR]
[TD="align: right"]01/12/18[/TD]
[TD="align: right"]01/12/18[/TD]
[TD]Active[/TD]
[TD="align: right"]1[/TD]
[TD]TJ1[/TD]
[TD]Tom[/TD]
[TD]Jones[/TD]
[TD]Sr.[/TD]
[TD]Tom Jones Sr.[/TD]
[TD]Tommy[/TD]
[/TR]
[TR]
[TD="align: right"]01/12/18[/TD]
[TD="align: right"]01/12/18[/TD]
[TD]Active[/TD]
[TD="align: right"]2[/TD]
[TD]ND2[/TD]
[TD]Nancy[/TD]
[TD]Drew[/TD]
[TD][/TD]
[TD]Nancy Drew[/TD]
[TD]Nancy Drew[/TD]
[/TR]
</tbody>[/TABLE]

Stats Sheet
[TABLE="class: grid, width: 700"]
<colgroup><col span="2"><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Today[/TD]
[TD]Updated[/TD]
[TD]Client ID[/TD]
[TD]Name[/TD]
[TD]Entry Type[/TD]
[TD]Height[/TD]
[TD]Weight[/TD]
[TD]Weight Change[/TD]
[/TR]
[TR]
[TD="align: right"]01/12/18[/TD]
[TD="align: right"]01/12/18[/TD]
[TD]ND2[/TD]
[TD]Nancy Drew[/TD]
[TD]Initial[/TD]
[TD]5'5"[/TD]
[TD="align: right"]125.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD="align: right"]01/12/18[/TD]
[TD="align: right"]01/11/18[/TD]
[TD]TJ1[/TD]
[TD]Tom Jones Sr.[/TD]
[TD]Initial[/TD]
[TD]5'11"[/TD]
[TD="align: right"]175.00[/TD]
[TD="align: right"]50.00[/TD]
[/TR]
[TR]
[TD="align: right"]01/12/18[/TD]
[TD="align: right"]01/12/18[/TD]
[TD]TJ1[/TD]
[TD]Tom Jones Sr.[/TD]
[TD]Update[/TD]
[TD]5'11"[/TD]
[TD="align: right"]170.00[/TD]
[TD="align: right"]-5.00[/TD]
[/TR]
</tbody>[/TABLE]

I was hoping to enter a formula via a macro that runs when a Client is entered.

Any suggestions?

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "Client Summary" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a Client ID in column D and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim foundId As Range
    Set foundId = Sheets("Bios").Range("E:E").Find(Target, LookIn:=xlValues, lookAt:=xlWhole)
    If Not foundId Is Nothing Then
        Target.Offset(0, 2) = foundId.Offset(0, 5)
    Else
        MsgBox ("Client ID not found in Bios sheet.")
    End If
    Set foundId = Sheets("Stats").Range("C:C").Find(What:=Target, LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
    If Not foundId Is Nothing Then
        Target.Offset(0, 3) = foundId.Offset(0, 5)
    Else
        MsgBox ("Client ID not found in Stats sheet.")
    End If
    Application.ScreenUpdating = True
End Sub

 

[reberryjr]

@mumps, this is really cool, but I don't think it gives me what I need. I'm looking for something that's going to continuously update as the data in the other sheets evolves. I'll be searching for MAX and MIN values on some sheets, and a dynamic amount of sheets.

However, this seems to be a bit cleaner than a bunch of Index/Match/Min/Max formulas (that I'm struggling to create).

I'm heading to the gym soon, but I'm going to see if I can fiddle with this code to replace the formulas. I'm thinking that for max and min, maybe I could declare row names and then leverage that in the code. Not sure though.

I very much appreciate the response!!!

 

[mumps]

My pleasure.

 

[reberryjr]

Would this seem like a road I should travel down when I get back?

I'm thinking that for max and min, maybe I could declare row names and then leverage that in the code

 

[mumps]

Without seeing your workbook and not knowing exactly what you want to do, it's difficult to tell what the best approach would be. It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[reberryjr]

@mumps I uploaded a copy to Box. The general idea is that as new clients are on-boarded, they get their own worksheet, named after their assigned unique Client ID (this is what I'm using to match everything on). While their individual worksheets will track details about the services they've signed up for, different stats are tracked on the Stats sheet (everyone is combined here). The EU isn't overly computer literate, so I want to prevent them from having to touch a lot of data, outside of userforms. The Client Summary sheet is what the EU will be able to play with the most. With that being said, I want to bring over the truly key elements that the EU will review the most, onto this sheet. I haven't fully planned out another step yet (because I don't truly know the capabilities of VBA), but I might use the Client Summary sheet and/or other sheets to drive some charts and/or pivots.

I'm happy to research paths and revert if I'm unable to figure something out. I just don't know what the path is. Hopefully that makes sense.

https://app.box.com/s/xmn52r728tq5gwu7xrzpe9az6qk0s6j6

 

[mumps]

It is difficult to follow what you want to do because you have many userforms and many controls with a code attached to each one. I'm not sure that I can help at this point but if it's possible for you to take me through the process, step-by-step, using one example of a new client being added, I will have a look to see if I can help in any way.

 

[reberryjr]

@mumps I was able to figure out some of the issues I was having (YAY!). I started a new post, that lists the remaining issues that I'm having, along with a link to the most up to date file.

https://www.mrexcel.com/forum/excel...red-into-sheets-via-userform.html#post4986929