Comparing Data Across Sheets, with Multiple Variables

[reberryjr]

I have two issues that are very similar, so I'm combining them into a single post.

Issue 1 - I have two sheets that I'm working with. One sheet is entitled "Bios", and the other is entitled "Stats". Both sheets have "Client ID" and "Weight" columns. What I'd like to do is, update the "Weight" column on the "Bios" sheet to the difference of the the "Weight" values on the "Stats" sheet. The "Stats" sheet has an "Updated" column, which I need to leverage. So I want the "Height" value from the MAX "Updated" date to be subtracted from the MIN "Updated" date on the "Stats" sheet.

I've included mock tables of both sheets below. Based on the 2nd table, the expected result in the 1st table would be "21.40". I'm thinking a formula would have some nesting, including a VLOOKUP; but I'm not sure how to account for the identification and comparison of the dates. If VBA is required, I'm fine with that (probably prefer it since I'm trying to learn it).

[TABLE="class: cms_table_grid, width: 200"]
<tbody>[TR]
[TD]Client ID[/TD]
[TD]Weight[/TD]
[/TR]
[TR]
[TD]RB1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: cms_table_grid, width: 220"]
<tbody>[TR]
[TD]Updated[/TD]
[TD]Client ID[/TD]
[TD]Weight[/TD]
[/TR]
[TR]
[TD]12/30/17[/TD]
[TD]RB1[/TD]
[TD]271.20[/TD]
[/TR]
[TR]
[TD]01/10/18[/TD]
[TD]RB1[/TD]
[TD]265.80[/TD]
[/TR]
[TR]
[TD]01/25/18[/TD]
[TD]RB1[/TD]
[TD]252.40[/TD]
[/TR]
[TR]
[TD]01/31/18[/TD]
[TD]RB1[/TD]
[TD]249.80[/TD]
[/TR]
</tbody>[/TABLE]


Issue 2 - Very much the same as the first issue, but I need to compare against multiple sheets, that may not be created yet. Every time a new Client is added, they get their own sheet, named the same as the "Client ID". What I'd be looking to do is, pull a specific value from whatever sheet where the sheet name equals the "Client ID". In this case, I'd be looking for the "Pymt Status" in the last row, on the sheet named "RB1". I can code for the last row part, but the rest, I'm at a loss.

[TABLE="class: cms_table_grid, width: 200"]
<tbody>[TR]
[TD]Client ID[/TD]
[TD]Pymt Status[/TD]
[/TR]
[TR]
[TD]RB1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JB2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Long post, and hopefully I was descriptive enough. Any and all constructive feedback is appreciated!

 

[NdNoviceHlp]

http://www.vbaexpress.com/forum/sho...ng-Data-Across-Sheets-with-Multiple-Variables
Please review the forum etiquette stuff. I'm guessing that U have no replies because U haven't been that specific. U also added height to the mix when I think U meant weight. Leverage also has a usual meaning that I don't believe is what U intend. Where is your data located and where does the output go (ie. column & row)? Why R U adding new sheets for every client... U may end up with hundreds/thousands of sheets? Anyways, this task is not that difficult in VBA. Consider setting up your wb with a minimum of sheets... there R hundreds of columns and thousands of rows available in each sheet. Good luck. Dave

 

[reberryjr]

Thanks for the response. I've addressed each of your points/questions below:

1. Forum etiquette: Yes, I posted the same question on two different forums. I was not aware that was an issue.
2. Height vs. Weight: Yes, I inadvertently used "Height", when I meant to use "Weight".
3. Leverage: Yes, I meant to use this word, as I need to "leverage" all of the field contents to perform the correct calculation.
4. Data location and output: I didn't provide all of that information, as I'm not really looking for someone to write the code for me. That won't help me learn. What I'm looking for is guidance on what I should research, so I can try and build the code on my own. In the event that I hit a snag, I would reply to the applicable thread and ask for guidance again.
5. Client Sheet Count: Each Client gets their own sheet so that I can accurately capture all of the changes in their financial obligations (adding or removing services, payment due dates, payments made, balance, payment status, etc.). We don't anticipate the sheet count being greater than 40 to 50. If it should become more than that, I would switch the application to something else, probably Access.

Regards