Retrieving Data from Multiple Sheets on Matching Criteria

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
714
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.
Code:
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
 
Upvote 0
@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!!!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
@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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,307
Members
452,633
Latest member
DougMo

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
Back
Top