Formulas Entered into Sheets via UserForm

[reberryjr]

Here's my challenge. When the EU (End User) is adding a new Client, they do so via a UserForm (frm_AddNew). When they click the cmd_Submit button, the UserForm data is entered onto various sheets, and a new sheet is created to hold their Service information. This new sheet is named after their assigned Client ID. Because I want to limit the data that the EU actually "touches", I want key information to map over to the Client Summaries sheet.

Goal 1 - I'd like to create a formula that can be added to column F on the Client Summaries sheet, that will identify the first instance of column BO on the particular Client's sheet. In the attached, I would want F2 on the Client Summaries sheet to reflect 14 from column BO on sheet TJ1. I do have some code that does some of this, dealing with other sheets. I tried to leverage that code in Macro3, but it isn't working.
Challenge - Implementing code that's going to know what sheet to go to, find column BO and enter the first instance where the value is >0. If there is no value > 0, then return a value of 0.

Goal 2 - Accurately calculate the difference in fields on the Stats tab (Ex: Weight). The calc should be the value of the last instance of each Client ID, from the first instance of each Client ID (column C). On the attached, it would be H5-H3. The Stats sheet is already coded to auto-sort by Client ID, then by Update Date when the sheet is activated, so it should always be in the proper order. The expectation is that I5 on sheet TJ1, would read -30.00.
Challenge - Implementing code that identifies the min update date, the max update and then calculates the value of column H between the two rows.

Hopefully the goals and challenges are descriptive enough.

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

 

[mumps]

I need some clarification when you say

Implementing code that's going to know what sheet to go to, find column BO and enter the first instance where the value is >0. If there is no value > 0, then return a value of 0.

In the last file you uploaded, in all three client sheets, the first instance in column BO that is >0 happens to be in cell BO2. So you would want the macro to return 15, 15 and 107 for the sheets respectively. Is this correct? It could be that sometimes rows 2,3 and 4 would have a zero and the first value >0 would be in BO5 in which case you would want the macro to return the value from BO5. Is this correct? Also, if all the values in column BO are equal to zero, then you want the macro to return a value of 0. Is this correct?
Regarding the second macro, you said in your original post:

The expectation is that I5 on sheet TJ1, would read -30.00.

You want to return the value to column I of the Client Summaries sheet instead. Is this correct?

 

[reberryjr]

In the last file you uploaded, in all three client sheets, the first instance in column BO that is >0 happens to be in cell BO2. So you would want the macro to return 15, 15 and 107 for the sheets respectively. Is this correct? It could be that sometimes rows 2,3 and 4 would have a zero and the first value >0 would be in BO5 in which case you would want the macro to return the value from BO5. Is this correct? Also, if all the values in column BO are equal to zero, then you want the macro to return a value of 0. Is this correct?

The way the workbook is structured, when any of the 5 service payments becomes Late or Paid (value in BX), another line is entered below, because it is time for the next payment(s) due to be documented. So, if row 2 (the first payment due) is Paid, but the 2nd payment period is entered into the next row. If this continues, and the 4th payment period (row 5) becomes Late, that would have the first instance of a value > 0 in column BO. (For full transparency, the same theory will apply to columns BQ, BS, BU and BW. I'm hoping to extrapolate the coding for BO to the other columns.)

You want to return the value to column I of the Client Summaries sheet instead. Is this correct?

The value of column I from Stats should go to column M of Client Summaries. Now, I'm still having issues getting the correct formula into the cell on Stats, but I'm working on that.

Can't tell you enough how much I appreciate the help!

 

[mumps]

I'm sorry but I find this more confusing. In reference to the first macro, I don't see any cells that have "Paid" in them so I can't see what effect that would have on your data in BO. Instead of describing things in general, maybe it would be helpful if you used a few examples from your data, referring to specific cells, rows, worksheets, etc. Also. if you could answer the questions I had in my previous response, that might help. Regarding the second macro, you say that you want the value from column I of Stats while initially you said you wanted the difference calculated from column H. It's difficult to follow when information keeps changing.

 

[reberryjr]

My work laptop won't let me access Box to make sure I'm working with the same file that I sent you, so I'll have to try and regroup this evening.

 

[reberryjr]

@mumps, I haven't forgotten about this. I'm taking some more time to re-examine the structure and the ask from the EU, so that I don't waste any more of your (or anyone else's) time.

 

[mumps]

No problem.

 

[reberryjr]

@mumps So, as I indicated yesterday, I slowed down a bit, got with the EU and finalized (I hope!) the layout and info they want to see. With that being said, I was able to solve my need through a structure change and some INDEX, MAX, MATCH, IF and INDIRECT formulae. They're getting placed in the worksheet via a macro, but it was a great learning experience, as I hadn't worked with INDIRECT before. I have saved the code you provided, as I think I'll find it useful in the future.

A sincere thank you for your help, and again, I apologize for the confusion on my part!

 

[mumps]

My pleasure.