Resource Issue with Excel

[reberryjr]

I'm in dire need of some help here. I'm getting the "Excel Ran out of Resources" error. I've googled a good bit, and it seems to have something to do with this formula.
=MAX(IF(INDIRECT("'"&C2&"'!D:D")=Summaries!C2,INDIRECT("'"&C2&"'!I:I")))

Right now, I have about 20 of these formulas on one sheet. I anticipate there being roughly 50 records on that sheet. I haven't the foggiest idea on where to start, to find a different formula to use, or route to go, to fix this issue. My application is essentially useless until I can fix this.

Any constructive help would be very much appreciated!

 

[thisoldman]

Some of the problem might be the whole column references, D:D and I:I. Each reference is over one million cells. Try to limit the range to something smaller, say D1:D1000 and I1:I1000—the actual used region and some reasonable overage as a cushion.

 

[reberryjr]

I thought about that, but I was trying to find a way to incorporate Last Row into the code. I'm not sure how to go about that though.

 

[offthelip]

What are you expecting that formula to produce? it seems a a bit odd to me, assuming there is a sheet name in C2, I read this as you want the maximum value of an if statement, I thought an if statement always comes up with one value anyway. then you seem to be equating a single cell (C2) to a whole column (D) on different sheets and returning a whole column. Or have I totally misunderstood the equation? ARe you entering this as an array formula??

I would expect to be able to rewrite with index and match if only I could understand what you are trying to do

 

[reberryjr]

It is a bit wonky. Here's what happens...when a Client is brought on board, they get their own worksheet. That worksheet is named the same thing as what their unique Client ID is. Because the code is being entered before the Client's sheet is actually created, it was recommended that I use the subject code.

Essentially, it's saying, "Find the Sheet name with the same value as C2 on the Summary sheet and retrieve the max value in column I of the Client's sheet.".

I was able to sort of solve the resource issue by disabling the multi-thread calculation, but it still runs very slow. This is concerning since there are only 2 test Clients in there now, and I anticipate anywhere from 50 - 80 total Clients. That's going to result in a lot of these formulas being present.

Hopefully that helps. Also, here's an example of the formulas being entered, when a Client is brought on board. And yes, Access would probably be a better tool to use, but I'm using this as a way to teach myself VBA in Excel, since my Company frowns on the use of Access.

Private Sub cmd_Submit_Click()

Application.ScreenUpdating = False


Dim cRefCat, cBiosNickname, cGender, cFacilitator, cYesNo, cPymtFreq, PymtFindRow As Range
Dim cSht As String
Dim UpdateRow As Long
Dim ws1, ws2, ws3, ws4, ws5, ws6, ws7, ws8, ws9 As Worksheet
Dim Response As VbMsgBoxResult


Set ws1 = ThisWorkbook.Sheets("Displays")
Set ws2 = ThisWorkbook.Sheets("Management")
Set ws3 = ThisWorkbook.Sheets("Summaries")
Set ws4 = ThisWorkbook.Sheets("Bios")
Set ws5 = ThisWorkbook.Sheets("Stats")
Set ws6 = ThisWorkbook.Sheets("Appt Tracker")
Set ws7 = ThisWorkbook.Sheets("Pymt Tracker")
Set ws8 = ThisWorkbook.Sheets("Financials")
Set ws9 = ThisWorkbook.Sheets("Variables")




LastRow3 = ws3.Range("C" & Rows.Count).End(xlUp).Row
LastRow4 = ws4.Range("E" & Rows.Count).End(xlUp).Row
LastRow5 = ws5.Range("C" & Rows.Count).End(xlUp).Row
LastRow6 = ws6.Range("C" & Rows.Count).End(xlUp).Row
LastRow7 = ws7.Range("C" & Rows.Count).End(xlUp).Row
LastRow8 = ws8.Range("D" & Rows.Count).End(xlUp).Row


Select Case True
'Throws an error if the First Name is left blank.
Case Me.txt_First.Value = ""
    Response = MsgBox("Please enter the Client's first name.")
    If Response = vbOK Then Me.txt_First.SetFocus
    Exit Sub
'Throws an error if the Last Name is left blank.
Case Me.txt_Last.Value = ""
    Response = MsgBox("Please enter the Client's last name.")
    If Response = vbOK Then Me.txt_Last.SetFocus
    Exit Sub
'Throws an error if the Nickname is left blank.
Case Me.txt_Nickname.Value = ""
    Response = MsgBox("Please enter a nickname for the Client.")
    If Response = vbOK Then Me.txt_Nickname.SetFocus
    Exit Sub
'Throws an error if the RefCat is left blank.
Case Me.cobo_RefCat.Value = "Select"
    Response = MsgBox("Please select a referral category for this Client.")
    If Response = vbOK Then Me.cobo_RefCat.SetFocus
    Exit Sub
'Throws an error if the Ref Nickname is left blank.
Case Me.cobo_RefNickname.Value = "Select"
    Response = MsgBox("Please select a nickname for who referred the Client to BBT.")
    If Response = vbOK Then Me.cobo_RefNickname.SetFocus
    Exit Sub
'Throws an error if the Email is left blank.
Case Me.txt_Email.Value = ""
    Response = MsgBox("Please enter the Client's email address.")
    If Response = vbOK Then Me.txt_Email.SetFocus
    Exit Sub
'Throws an error if the Gender is left blank.
Case Me.cobo_Gender = "Select"
    Response = MsgBox("Please select the Client's gender.")
    If Response = vbOK Then Me.cobo_Gender.SetFocus
    Exit Sub
'Throws an error if the DoB is left blank.
Case Me.txt_DoB = ""
    Response = MsgBox("Please enter the Client's DoB.")
    If Response = vbOK Then Me.txt_DoB.SetFocus
    Exit Sub
'Throws an error if the Signup age is left blank.
Case Me.txt_SignupAge = ""
    Response = MsgBox("Please enter the Client's age at signup.")
    If Response = vbOK Then Me.txt_SignupAge.SetFocus
    Exit Sub
'Throws an error if the Phone is left blank.
Case Me.txt_Phone = ""
    Response = MsgBox("Please enter the Client's phone number.")
    If Response = vbOK Then Me.txt_Phone.SetFocus
    Exit Sub
'Throws an error if the Notes are left blank.
Case Me.txt_Notes = ""
    Response = MsgBox("Please enter pertinent notes about the Client.")
    If Response = vbOK Then Me.txt_Notes.SetFocus
    Exit Sub
'Throws an error if the Stats Taken field is left blank.
Case Me.txt_StatsTaken = ""
    Response = MsgBox("Please enter the date that the Client's measurements were taken.")
    If Response = vbOK Then Me.txt_StatsTaken.SetFocus
    Exit Sub
'Throws an error if the Facilitator is left blank.
Case Me.cobo_Facilitator = "Select"
    MsgBox ("Please select the category of the person that took the Client's measurements.")
    If Response = vbOK Then Me.cobo_Facilitator.SetFocus
    Exit Sub
'Throws an error if the Height field is left blank.
Case Me.txt_Height = ""
    Response = MsgBox("Please enter a Height value for the Client.")
    If Response = vbOK Then Me.txt_Height.SetFocus
    Exit Sub
'Throws an error if the Weight is left blank.
Case Me.txt_Weight = ""
    Response = MsgBox("Please enter the Client's weight.")
    If Response = vbOK Then Me.txt_Weight.SetFocus
    Exit Sub
'Throws an error if the Body Fat is left blank.
Case Me.txt_BodyFat = ""
    Response = MsgBox("Please enter the Client's body fat measurement, using numbers only.")
    If Response = vbOK Then Me.txt_BodyFat.SetFocus
    Exit Sub
'Throws an error if the BMI is left blank.
Case Me.txt_BMI = ""
    Response = MsgBox("Please enter the Client's BMI, using numbers only.")
    If Response = vbOK Then Me.txt_BMI.SetFocus
    Exit Sub
'Throws an error if the Chest is left blank.
Case Me.txt_Chest = ""
    Response = MsgBox("Please enter the Client's chest measurement, using numbers only.")
    If Response = vbOK Then Me.txt_Chest.SetFocus
    Exit Sub
'Throws an error if the Waist is left blank.
Case Me.txt_Waist = ""
    Response = MsgBox("Please enter the Client's waist measurement, using numbers only.")
    If Response = vbOK Then Me.txt_Waist.SetFocus
    Exit Sub
'Throws an error if the Hips is left blank.
Case Me.txt_Hips = ""
    Response = MsgBox("Please enter the Client's hips measurement, using numbers only.")
    If Response = vbOK Then Me.txt_Hips.SetFocus
    Exit Sub
'Throws an error if the Date Received is left blank.
Case Me.txt_DateRcvd = ""
    Response = MsgBox("Please enter the date the Client tendered funds for services selected, in MM/DD/YY format.")
    If Response = vbOK Then Me.txt_DateRcvd.SetFocus
    Exit Sub
'Throws an error if the Amount Received is left blank.
Case Me.txt_AmtRcvd = ""
    Response = MsgBox("Please enter the amount of funds received from the Client, using numbers only.")
    If Response = vbOK Then Me.txt_AmtRcvd.SetFocus
    Exit Sub
'Throws an error if the Total Payment Method is left blank.
Case Me.cobo_TotalPymtMethod = ""
    Response = MsgBox("Please select the method the Client used to tender funds.")
    If Response = vbOK Then Me.cobo_TotalPymtMethod.SetFocus
    Exit Sub
End Select
'Throws an error if the Suffix is left blank.
If Len(Me.txt_Suff.Value) = 0 Then
    Response = Response = MsgBox("Does the Client have a suffix?", vbYesNo)
    If Response = vbYes Then
        Me.txt_Suff.SetFocus
        Exit Sub
    End If
End If
'Throws an error if the Referral Category = Client and the Ref Id is left blank.
If (Me.cobo_RefCat.Value) = "Select" And Len(Me.txt_RefID.Value) = 0 Then
    Response = MsgBox("Please enter the correct Referral ID for the Client that referred this Client to BBT.")
    If Response = vbOK Then Me.txt_RefID.SetFocus
    Exit Sub
End If
'Throws an error if the any part of the Address is not null, and any part other than Street 2 is null.
If (Not Len(Me.txt_Street1.Value) = 0 Or Not Len(Me.txt_Street2.Value) = 0 Or Not Len(Me.txt_City.Value) = 0 Or Not (Me.cobo_ST.Value) = "Select" Or Not Len(Me.txt_Zip.Value) = 0) And _
(Len(Me.txt_Street1.Value) = 0 Or Len(Me.txt_City.Value) = 0 Or (Me.cobo_ST.Value) = "Select" Or Len(Me.txt_Zip.Value) = 0) Then
    Response = MsgBox("Please complete the partial address entered.")
    If Response = vbOK Then Me.txt_Street1.SetFocus
    Exit Sub
End If
'Prompts the User to verify that the Address wasn't provided.
If Len(Me.txt_Street1.Value) = 0 And Len(Me.txt_Street2.Value) = 0 And Len(Me.txt_City.Value) = 0 And (Me.cobo_ST.Value) = "Select" And Len(Me.txt_Zip.Value) = 0 Then
    Response = MsgBox("Did the Client provide their mailing address?", vbYesNo)
    If Response = vbYes Then
        Me.txt_Street1.SetFocus
        Exit Sub
    End If
End If
'Throws an error if both Bicep fields are blank.
If Len(Me.txt_BicepL.Value) = 0 And Len(Me.txt_BicepR) = 0 Then
    Response = MsgBox("You must enter a value in at least one bicep field.")
    If Response = vbOK Then Me.txt_BicepL.SetFocus
    Exit Sub
End If
'Throws an error if both Thigh fields are blank.
If Len(Me.txt_ThighL.Value) = 0 And Len(Me.txt_ThighR) = 0 Then
    Response = MsgBox("You must enter a value in at least one thigh field.")
    If Response = vbOK Then Me.txt_ThighL.SetFocus
    Exit Sub
End If
'Throws an error if both Calf fields are blank.
If Len(Me.txt_CalfL.Value) = 0 And Len(Me.txt_CalfR) = 0 Then
    Response = MsgBox("You must enter a value in at least one calf field.")
    If Response = vbOK Then Me.txt_CalfL.SetFocus
    Exit Sub
End If
'Throws an error if partial data was entered in the field series.
If (Not Len(Me.txt_DPDate.Value) = 0 Or Val(Me.txt_DPAmt.Text) > 0 Or Val(Me.txt_DPPaid.Text) > 0) And _
(Len(Me.txt_DPDate.Value) = 0 Or Val(Me.txt_DPAmt.Text) = 0) Then
    Response = MsgBox("Please complete the partial data entered in the DP section.")
    If Response = vbOK Then Me.txt_DPDate.SetFocus
    Exit Sub
End If
'Prompts the User to verify if the Client signed up for this service.
If Len(Me.txt_DPDate.Value) = 0 And Len(Me.txt_DPAmt.Value) = 0 And Len(Me.txt_DPPaid.Value) = 0 Then
    Response = MsgBox("Did the Client sign up for a DP?", vbYesNo)
    If Response = vbYes Then
        Me.txt_DPDate.SetFocus
        Exit Sub
    End If
End If
'Throws an error if partial data was entered in the field series.
If (Not Len(Me.txt_TPDate.Value) = 0 Or Val(Me.txt_TPAmt.Text) > 0 Or Val(Me.txt_TPPaid.Text) > 0) And _
(Len(Me.txt_TPDate.Value) = 0 Or Val(Me.txt_TPAmt.Text) = 0) Then
    Response = MsgBox("Please complete the partial data entered in the TP section.")
    If Response = vbOK Then Me.txt_TPDate.SetFocus
    Exit Sub
End If
'Prompts the User to verify if the Client signed up for this service.
If Len(Me.txt_TPDate.Value) = 0 And Len(Me.txt_TPAmt.Value) = 0 And Len(Me.txt_TPPaid.Value) = 0 Then
    Response = MsgBox("Did the Client sign up for a TP?", vbYesNo)
    If Response = vbYes Then
        Me.txt_TPDate.SetFocus
        Exit Sub
    End If
End If
'Throws an error if partial data was entered in the field series.
If (Not Len(Me.txt_TRStart.Value) = 0 Or Not Len(Me.txt_TRNext.Value) = 0 Or Val(Me.txt_TRPymtAmt.Text) <> 0 Or Not Len(Me.cobo_TRFreq.Value) = 0 Or _
    Val(Me.txt_TRAmtPaid.Value) <> 0) And (Len(Me.txt_TRStart.Value) = 0 Or Len(Me.txt_TRNext.Value) = 0 Or Val(Me.txt_TRPymtAmt.Text) = 0 Or Len(Me.cobo_TRFreq.Value) = 0) Then
        Response = MsgBox("Please complete the partial information in the TR section.")
        If Response = vbOK Then Me.txt_TRStart.SetFocus
Exit Sub
End If
'Prompts the User to verify if the Client signed up for this service.
If Len(Me.txt_TRStart.Value) = 0 And Len(Me.txt_TRNext.Value) = 0 And Val(Me.txt_TRAmtPaid.Text) = 0 And Len(Me.cobo_TRFreq.Value) = 0 And Val(Me.txt_TRPymtAmt.Text) = 0 Then
    Response = MsgBox("Did the Client sign up for TR sessions?", vbYesNo)
    If Response = vbYes Then
        Me.txt_TRStart.SetFocus
        Exit Sub
    End If
End If
'Throws an error if partial data was entered in the field series.
If (Not Len(Me.txt_DCStart.Value) = 0 Or Not Len(Me.txt_DCNext.Value) = 0 Or Val(Me.txt_DCPymtAmt.Text) <> 0 Or Not Len(Me.cobo_DCFreq.Value) = 0 Or _
    Val(Me.txt_DCAmtPaid.Value) <> 0) And (Len(Me.txt_DCStart.Value) = 0 Or Len(Me.txt_DCNext.Value) = 0 Or Val(Me.txt_DCPymtAmt.Text) = 0 Or Len(Me.cobo_DCFreq.Value) = 0) Then
        Response = MsgBox("Please complete the partial information in the DC section.")
        If Response = vbOK Then Me.txt_DCStart.SetFocus
Exit Sub
End If
'Prompts the User to verify if the Client signed up for this service.
If Len(Me.txt_DCStart.Value) = 0 And Len(Me.txt_DCNext.Value) = 0 And Val(Me.txt_DCAmtPaid.Text) = 0 And Len(Me.cobo_DCFreq.Value) = 0 And Val(Me.txt_DCPymtAmt.Text) = 0 Then
    Response = MsgBox("Did the Client sign up for DC sessions?", vbYesNo)
    If Response = vbYes Then
        Me.txt_DCStart.SetFocus
        Exit Sub
    End If
End If
'Throws an error if partial data was entered in the field series.
If (Not Len(Me.txt_OCStart.Value) = 0 Or Not Len(Me.txt_OCNext.Value) = 0 Or Val(Me.txt_OCPymtAmt.Text) <> 0 Or Not Len(Me.cobo_OCFreq.Value) = 0 Or _
    Val(Me.txt_OCAmtPaid.Value) <> 0) And (Len(Me.txt_OCStart.Value) = 0 Or Len(Me.txt_OCNext.Value) = 0 Or Val(Me.txt_OCPymtAmt.Text) = 0 Or Len(Me.cobo_OCFreq.Value) = 0) Then
        Response = MsgBox("Please complete the partial information in the OC section.")
        If Response = vbOK Then Me.txt_OCStart.SetFocus
Exit Sub
End If
'Prompts the User to verify if the Client signed up for this service.
If Len(Me.txt_OCStart.Value) = 0 And Len(Me.txt_OCNext.Value) = 0 And Val(Me.txt_OCAmtPaid.Text) = 0 And Len(Me.cobo_OCFreq.Value) = 0 And Val(Me.txt_OCPymtAmt.Text) = 0 Then
    Response = MsgBox("Did the Client sign up for OC sessions?", vbYesNo)
    If Response = vbYes Then
        Me.txt_OCStart.SetFocus
        Exit Sub
    End If
End If
'Throws an error if partial data was entered in the field series.
If (Not Len(Me.txt_CTIStart.Value) = 0 Or Not Len(Me.txt_CTINext.Value) = 0 Or Val(Me.txt_CTIPymtAmt.Text) <> 0 Or Not Len(Me.cobo_CTIFreq.Value) = 0 Or _
    Val(Me.txt_CTIAmtPaid.Value) <> 0) And (Len(Me.txt_CTIStart.Value) = 0 Or Len(Me.txt_CTINext.Value) = 0 Or Val(Me.txt_CTIPymtAmt.Text) = 0 Or Len(Me.cobo_CTIFreq.Value) = 0) Then
        Response = MsgBox("Please complete the partial information in the CT-I section.")
        If Response = vbOK Then Me.txt_CTIStart.SetFocus
Exit Sub
End If
'Prompts the User to verify if the Client signed up for this service.
If Len(Me.txt_CTIStart.Value) = 0 And Len(Me.txt_CTINext.Value) = 0 And Val(Me.txt_CTIAmtPaid.Text) = 0 And Len(Me.cobo_CTIFreq.Value) = 0 And Val(Me.txt_CTIPymtAmt.Text) = 0 Then
    Response = MsgBox("Did the Client sign up for CT-I sessions?", vbYesNo)
    If Response = vbYes Then
        Me.txt_CTIStart.SetFocus
        Exit Sub
    End If
End If
'Throws an error if partial data was entered in the field series.
If (Not Len(Me.txt_CTOStart.Value) = 0 Or Not Len(Me.txt_CTONext.Value) = 0 Or Val(Me.txt_CTOPymtAmt.Text) <> 0 Or Not Len(Me.cobo_CTOFreq.Value) = 0 Or _
    Val(Me.txt_CTOAmtPaid.Value) <> 0) And (Len(Me.txt_CTOStart.Value) = 0 Or Len(Me.txt_CTONext.Value) = 0 Or Val(Me.txt_CTOPymtAmt.Text) = 0 Or Len(Me.cobo_CTOFreq.Value) = 0) Then
        Response = MsgBox("Please complete the partial information in the CT-O section.")
        If Response = vbOK Then Me.txt_CTOStart.SetFocus
Exit Sub
End If
'Prompts the User to verify if the Client signed up for this service.
If Len(Me.txt_CTOStart.Value) = 0 And Len(Me.txt_CTONext.Value) = 0 And Val(Me.txt_CTOAmtPaid.Text) = 0 And Len(Me.cobo_CTOFreq.Value) = 0 And Val(Me.txt_CTOPymtAmt.Text) = 0 Then
    Response = MsgBox("Did the Client sign up for CT-O sessions?", vbYesNo)
    If Response = vbYes Then
        Me.txt_CTOStart.SetFocus
        Exit Sub
    End If
End If




Msg1 = MsgBox("Were there any data entry errors found?", vbYesNo, "Data Entry Validation")
If Msg1 = vbYes Then
    Me.txt_First.SetFocus
Else


ws4.Range("A" & LastRow4 + 1).Value = "=Today()"
ws4.Range("B" & LastRow4 + 1).Value = Now() 'Should this be Now or User Defined?
ws4.Range("C" & LastRow4 + 1).Value = "Active"
ws4.Range("D" & LastRow4 + 1).Value = CInt(Me.txt_Key)
With ws4.Range("E" & LastRow4 + 1)
    .Hyperlinks.Add _
    anchor:=.Offset(), _
    Address:="", _
    SubAddress:="'" & Me.txt_ClientID.Value & "'!CE1", _
    TextToDisplay:=Me.txt_ClientID.Value
End With
ws4.Range("F" & LastRow4 + 1).Value = Me.txt_First
ws4.Range("G" & LastRow4 + 1).Value = Me.txt_Last
ws4.Range("H" & LastRow4 + 1).Value = Me.txt_Suff
ws4.Range("I" & LastRow4 + 1).Value = Me.txt_Name
ws4.Range("J" & LastRow4 + 1).Value = Me.txt_Nickname
ws4.Range("K" & LastRow4 + 1).Value = Me.cobo_Gender
ws4.Range("L" & LastRow4 + 1).Value = CDate(Me.txt_DoB)
ws4.Range("M" & LastRow4 + 1).Value = CInt(Me.txt_SignupAge)
ws4.Range("N" & LastRow4 + 1).Value = "=IF(RC[-2]="""","""",INT(RC[-12]-RC[-2])/365.25)"
ws4.Range("O" & LastRow4 + 1).Value = (Me.txt_Phone)
With ws4.Range("P" & LastRow4 + 1)
    .Hyperlinks.Add _
    anchor:=.Offset(), _
    Address:="mailto:" & Me.txt_Email.Value, _
    TextToDisplay:=Me.txt_Email.Value
End With
ws4.Range("Q" & LastRow4 + 1).Value = Me.txt_Street1
ws4.Range("R" & LastRow4 + 1).Value = Me.txt_Street2
ws4.Range("S" & LastRow4 + 1).Value = Me.txt_City
If Me.cobo_ST.Value = "Select" Then
    ws4.Range("T" & LastRow4 + 1).Value = ""
Else
    ws4.Range("T" & LastRow4 + 1).Value = Me.cobo_ST
End If
ws4.Range("U" & LastRow4 + 1).Value = CStr(Me.txt_Zip)
ws4.Range("V" & LastRow4 + 1).Value = Me.cobo_RefCat
ws4.Range("W" & LastRow4 + 1).Value = Me.txt_RefID
ws4.Range("X" & LastRow4 + 1).Value = Me.cobo_RefNickname
ws4.Range("Y" & LastRow4 + 1).Value = "=COUNTIF(C[-1],RC[-15])"
ws4.Range("Z" & LastRow4 + 1).Value = Me.txt_Notes


ws5.Range("A" & LastRow5 + 1).Value = "=Today()"
ws5.Range("B" & LastRow5 + 1).Value = Now() 'Should this be Now or User Defined?
ws5.Range("C" & LastRow5 + 1).Value = Me.txt_ClientID
ws5.Range("D" & LastRow5 + 1).Value = Me.txt_Name
ws5.Range("E" & LastRow5 + 1).Value = Me.txt_Nickname
ws5.Range("F" & LastRow5 + 1).Value = "Initial"
ws5.Range("G" & LastRow5 + 1).Value = Me.txt_PicsDate.Value
ws5.Range("H" & LastRow5 + 1).Value = Me.cobo_Facilitator
ws5.Range("I" & LastRow5 + 1).Value = CDate(Me.txt_StatsTaken)
ws5.Range("J" & LastRow5 + 1).Value = Me.txt_Height
ws5.Range("K" & LastRow5 + 1).Value = Me.txt_Weight.Value
ws5.Range("L" & LastRow5 + 1).Value = "=IF(C[-9]=R[-1]C[-9],IF(ISNUMBER(R[-1]C[-1]),RC[-1]-R[-1]C[-1],0),0)"
ws5.Range("M" & LastRow5 + 1).Value = Me.txt_BodyFat.Value
ws5.Range("N" & LastRow5 + 1).Value = "=IF(C[-11]=R[-1]C[-11],IF(ISNUMBER(R[-1]C[-1]),RC[-1]-R[-1]C[-1],0),0)"
ws5.Range("O" & LastRow5 + 1).Value = Me.txt_BMI.Value
ws5.Range("P" & LastRow5 + 1).Value = "=IF(C[-13]=R[-1]C[-13],IF(ISNUMBER(R[-1]C[-1]),RC[-1]-R[-1]C[-1],0),0)"
ws5.Range("Q" & LastRow5 + 1).Value = Me.txt_Chest.Value
ws5.Range("R" & LastRow5 + 1).Value = "=IF(C[-15]=R[-1]C[-15],IF(ISNUMBER(R[-1]C[-1]),RC[-1]-R[-1]C[-1],0),0)"
ws5.Range("S" & LastRow5 + 1).Value = Me.txt_Waist.Value
ws5.Range("T" & LastRow5 + 1).Value = "=IF(C[-17]=R[-1]C[-17],IF(ISNUMBER(R[-1]C[-1]),RC[-1]-R[-1]C[-1],0),0)"
ws5.Range("U" & LastRow5 + 1).Value = Me.txt_Hips.Value
ws5.Range("V" & LastRow5 + 1).Value = "=IF(C[-19]=R[-1]C[-19],IF(ISNUMBER(R[-1]C[-1]),RC[-1]-R[-1]C[-1],0),0)"
ws5.Range("W" & LastRow5 + 1).Value = Me.txt_BicepL.Value
ws5.Range("X" & LastRow5 + 1).Value = "=IF(C[-21]=R[-1]C[-21],IF(ISNUMBER(R[-1]C[-1]),RC[-1]-R[-1]C[-1],0),0)"
ws5.Range("Y" & LastRow5 + 1).Value = Me.txt_BicepR.Value
ws5.Range("Z" & LastRow5 + 1).Value = "=IF(C[-23]=R[-1]C[-23],IF(ISNUMBER(R[-1]C[-1]),RC[-1]-R[-1]C[-1],0),0)"
ws5.Range("AA" & LastRow5 + 1).Value = Me.txt_ThighL.Value
ws5.Range("AB" & LastRow5 + 1).Value = "=IF(C[-25]=R[-1]C[-25],IF(ISNUMBER(R[-1]C[-1]),RC[-1]-R[-1]C[-1],0),0)"
ws5.Range("AC" & LastRow5 + 1).Value = Me.txt_ThighR.Value
ws5.Range("AD" & LastRow5 + 1).Value = "=IF(C[-27]=R[-1]C[-27],IF(ISNUMBER(R[-1]C[-1]),RC[-1]-R[-1]C[-1],0),0)"
ws5.Range("AE" & LastRow5 + 1).Value = Me.txt_CalfL.Value
ws5.Range("AF" & LastRow5 + 1).Value = "=IF(C[-29]=R[-1]C[-29],IF(ISNUMBER(R[-1]C[-1]),RC[-1]-R[-1]C[-1],0),0)"
ws5.Range("AG" & LastRow5 + 1).Value = Me.txt_CalfR.Value
ws5.Range("AH" & LastRow5 + 1).Value = "=IF(C[-31]=R[-1]C[-31],IF(ISNUMBER(R[-1]C[-1]),RC[-1]-R[-1]C[-1],0),0)"


ws7.Range("A" & LastRow7 + 1).Value = "=Today()"
ws7.Range("B" & LastRow7 + 1).Value = Now()
ws7.Range("C" & LastRow7 + 1).Value = Me.txt_ClientID
ws7.Range("D" & LastRow7 + 1).Value = Me.txt_Name
ws7.Range("E" & LastRow7 + 1).Value = Me.txt_Nickname
ws7.Range("F" & LastRow7 + 1).Value = CDate(Me.txt_DateRcvd)
ws7.Range("G" & LastRow7 + 1).Value = CCur(Me.txt_AmtRcvd)
ws7.Range("H" & LastRow7 + 1).Value = Me.cobo_TotalPymtMethod
ws7.Range("I" & LastRow7 + 1).Value = "Initial payment by Client, for services rendered."


ws8.Range("A" & LastRow8 + 1).Value = "=Today()"
ws8.Range("B" & LastRow8 + 1).Value = Now() 'Should this be Now or User Defined?
ws8.Range("C" & LastRow8 + 1).Value = "Initial"
ws8.Range("D" & LastRow8 + 1).Value = Me.txt_ClientID
ws8.Range("E" & LastRow8 + 1).Value = Me.txt_Name
ws8.Range("F" & LastRow8 + 1).Value = Me.txt_Nickname
ws8.Range("G" & LastRow8 + 1).Value = "=IF(RC[3]="""",""Inactive"",IF(RC[4]>=RC[3],""Paid"",""Pending""))"
If Not Len(Me.txt_DPDate) = 0 Then ws8.Range("H" & LastRow8 + 1).Value = CDate(Me.txt_DPDate)
ws8.Range("I" & LastRow8 + 1).Value = "=RC[-8]-RC[-1]"
If Not Len(Me.txt_DPAmt) = 0 Then ws8.Range("J" & LastRow8 + 1).Value = CCur(Me.txt_DPAmt)
If Not Len(Me.txt_DPPaid) = 0 Then ws8.Range("K" & LastRow8 + 1).Value = CDate(Me.txt_DPPaid)
ws8.Range("L" & LastRow8 + 1).Value = "=RC[-2]-RC[-1]"
ws8.Range("M" & LastRow8 + 1).Value = "=IF(RC[-3]="""",0,IF(RC[-1]>0,RC[-12]-RC[-5],0))"
ws8.Range("N" & LastRow8 + 1).Value = "=IF(RC[3]="""",""Inactive"",IF(RC[5]=0,""Paid"",""Pending""))"
If Not Len(Me.txt_TPDate) = 0 Then ws8.Range("O" & LastRow8 + 1).Value = CDate(Me.txt_TPDate)
ws8.Range("P" & LastRow8 + 1).Value = "=RC[-15]-RC[-1]"
If Not Len(Me.txt_TPAmt) = 0 Then ws8.Range("Q" & LastRow8 + 1).Value = CCur(Me.txt_TPAmt)
If Not Len(Me.txt_TPPaid) = 0 Then ws8.Range("R" & LastRow8 + 1).Value = CDate(Me.txt_TPPaid)
ws8.Range("S" & LastRow8 + 1).Value = "=RC[-2]-RC[-1]"
ws8.Range("T" & LastRow8 + 1).Value = "=IF(RC[-3] = """",0,IF(RC[-1]>0,RC[-19]-RC[-5],0))"
ws8.Range("U" & LastRow8 + 1).Value = "=IF(RC[9]<>"""",""Inactive"",IF(RC[1]="""",""Inactive"",""Active""))"
If Not Len(Me.txt_TRStart) = 0 Then ws8.Range("V" & LastRow8 + 1).Value = CDate(Me.txt_TRStart)
ws8.Range("W" & LastRow8 + 1).Value = "=IF(RC[-1]="""",""0"",IF(RC[7]<>"""",RC[7]-RC[-1],RC[-22]-RC[-1]))"
If Not Len(Me.txt_TRNext) = 0 Then ws8.Range("X" & LastRow8 + 1).Value = CDate(Me.txt_TRNext)
If Not Len(Me.txt_TRPymtAmt) = 0 Then ws8.Range("Y" & LastRow8 + 1).Value = CCur(Me.txt_TRPymtAmt)
If Not Len(Me.cobo_TRFreq) = 0 Then ws8.Range("Z" & LastRow8 + 1).Value = Me.cobo_TRFreq
ws8.Range("AA" & LastRow8 + 1).Value = "=IF(RC[-6]=""Inactive"","""",IF(RC[-3]<>"""",RC[-3],IF(R[-1]C[-1]=""B"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+14),IF(R[-1]C[-1]=""W"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+7),IF(R[-1]C[-1]=""M"",DATE(YEAR(R[-1]C),MONTH(R[-1]C)+1,DAY(R[-1]C)))))))"
ws8.Range("AB" & LastRow8 + 1).Value = "=IF(RC[-7]=""Inactive"",0,IF(RC[-1]>RC[-27],0,IF(RC[-2]=""M"",RC[-3]/1,IF(RC[-2]=""B"",RC[-3]/2,IF(RC[-2]=""W"",RC[-3]/4,0)))))"
ws8.Range("AC" & LastRow8 + 1).Value = "=IF(RC[-8]=""Inactive"",0,IF(RC[-28]<rc[-2],0,if(rc[50]>0,RC[-28]-RC[-2],0)))"
'ws8.Range("AD" & LastRow8 + 1).Value  Nothing mapped to this field, as End Dates aren't populated when Clients are being added.
ws8.Range("AE" & LastRow8 + 1).Value = "=IF(RC[9]<>"""",""Inactive"",IF(RC[1]="""",""Inactive"",""Active""))"
If Not Len(Me.txt_DCStart) = 0 Then ws8.Range("AF" & LastRow8 + 1).Value = CDate(Me.txt_DCStart)
ws8.Range("AG" & LastRow8 + 1).Value = "=IF(RC[-1]="""",""0"",IF(RC[7]<>"""",RC[7]-RC[-1],RC[-32]-RC[-1]))"
If Not Len(Me.txt_DCNext) = 0 Then ws8.Range("AH" & LastRow8 + 1).Value = CDate(Me.txt_DCNext)
If Not Len(Me.txt_DCPymtAmt) = 0 Then ws8.Range("AI" & LastRow8 + 1).Value = CCur(Me.txt_DCPymtAmt)
If Not Len(Me.cobo_DCFreq) = 0 Then ws8.Range("AJ" & LastRow8 + 1).Value = Me.cobo_DCFreq
ws8.Range("AK" & LastRow8 + 1).Value = "=IF(RC[-6]=""Inactive"","""",IF(RC[-3]<>"""",RC[-3],IF(R[-1]C[-1]=""B"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+14),IF(R[-1]C[-1]=""W"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+7),IF(R[-1]C[-1]=""M"",DATE(YEAR(R[-1]C),MONTH(R[-1]C)+1,DAY(R[-1]C)))))))"
ws8.Range("AL" & LastRow8 + 1).Value = "=IF(RC[-7]=""Inactive"",0,IF(RC[-1]>RC[-37],0,IF(RC[-2]=""M"",RC[-4]/1,IF(RC[-2]=""B"",RC[-3]/2,IF(RC[-2]=""W"",RC[-3]/4,0)))))"
ws8.Range("AM" & LastRow8 + 1).Value = "=IF(RC[-8]=""Inactive"",0,IF(RC[-38]<rc[-2],0,if(rc[40]>0,RC[-38]-RC[-2],0)))"
'ws8.Range("AN" & LastRow8 + 1).Value  Nothing mapped to this field, as End Dates aren't populated when Clients are being added.
ws8.Range("AO" & LastRow8 + 1).Value = "=IF(RC[9]<>"""",""Inactive"",IF(RC[1]="""",""Inactive"",""Active""))"
If Not Len(Me.txt_OCStart) = 0 Then ws8.Range("AP" & LastRow8 + 1).Value = CDate(Me.txt_OCStart)
ws8.Range("AQ" & LastRow8 + 1).Value = "=IF(RC[-1]="""",""0"",IF(RC[7]<>"""",RC[7]-RC[-1],RC[-42]-RC[-1]))"
If Not Len(Me.txt_OCNext) = 0 Then ws8.Range("AR" & LastRow8 + 1).Value = CDate(Me.txt_OCNext)
If Not Len(Me.txt_OCPymtAmt) = 0 Then ws8.Range("AS" & LastRow8 + 1).Value = CCur(Me.txt_OCPymtAmt)
If Not Len(Me.cobo_OCFreq) = 0 Then ws8.Range("AT" & LastRow8 + 1).Value = Me.cobo_OCFreq
ws8.Range("AU" & LastRow8 + 1).Value = "=IF(RC[-6]=""Inactive"","""",IF(RC[-3]<>"""",RC[-3],IF(R[-1]C[-1]=""B"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+14),IF(R[-1]C[-1]=""W"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+7),IF(R[-1]C[-1]=""M"",DATE(YEAR(R[-1]C),MONTH(R[-1]C)+1,DAY(R[-1]C)))))))"
ws8.Range("AV" & LastRow8 + 1).Value = "=IF(RC[-7]=""Inactive"",0,IF(RC[-1]>RC[-47],0,IF(RC[-2]=""M"",RC[-3]/1,IF(RC[-2]=""B"",RC[-3]/2,IF(RC[-2]=""W"",RC[-3]/4,0)))))"
ws8.Range("AW" & LastRow8 + 1).Value = "=IF(RC[-8]=""Inactive"",0,IF(RC[-48]<rc[-2],0,if(rc[30]>0,RC[-48]-RC[-2],0)))"
'ws8.Range("AX" & LastRow8 + 1).Value  Nothing mapped to this field, as End Dates aren't populated when Clients are being added.
ws8.Range("AY" & LastRow8 + 1).Value = "=IF(RC[9]<>"""",""Inactive"",IF(RC[1]="""",""Inactive"",""Active""))"
If Not Len(Me.txt_CTIStart) = 0 Then ws8.Range("AZ" & LastRow8 + 1).Value = CDate(Me.txt_CTIStart)
ws8.Range("BA" & LastRow8 + 1).Value = "=IF(RC[-1]="""",""0"",IF(RC[7]<>"""",RC[7]-RC[-1],RC[-52]-RC[-1]))"
If Not Len(Me.txt_CTINext) = 0 Then ws8.Range("BB" & LastRow8 + 1).Value = CDate(Me.txt_CTINext)
If Not Len(Me.txt_CTIPymtAmt) = 0 Then ws8.Range("BC" & LastRow8 + 1).Value = CCur(Me.txt_CTIPymtAmt)
If Not Len(Me.cobo_CTIFreq) = 0 Then ws8.Range("BD" & LastRow8 + 1).Value = Me.cobo_CTIFreq
ws8.Range("BE" & LastRow8 + 1).Value = "=IF(RC[-6]=""Inactive"","""",IF(RC[-3]<>"""",RC[-3],IF(R[-1]C[-1]=""B"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+14),IF(R[-1]C[-1]=""W"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+7),IF(R[-1]C[-1]=""M"",DATE(YEAR(R[-1]C),MONTH(R[-1]C)+1,DAY(R[-1]C)))))))"
ws8.Range("BF" & LastRow8 + 1).Value = "=IF(RC[-7]=""Inactive"",0,IF(RC[-1]>RC[-57],0,IF(RC[-2]=""M"",RC[-3]/1,IF(RC[-2]=""B"",RC[-3]/2,IF(RC[-2]=""W"",RC[-3]/4,0)))))"
ws8.Range("BG" & LastRow8 + 1).Value = "=IF(RC[-8]=""Inactive"",0,IF(RC[-58]<rc[-2],0,if(rc[20]>0,RC[-58]-RC[-2],0)))"
'ws8.Range("BH" & LastRow8 + 1).Value  Nothing mapped to this field, as End Dates aren't populated when Clients are being added.
ws8.Range("BI" & LastRow8 + 1).Value = "=IF(RC[9]<>"""",""Inactive"",IF(RC[1]="""",""Inactive"",""Active""))"
If Not Len(Me.txt_CTOStart) = 0 Then ws8.Range("BJ" & LastRow8 + 1).Value = CDate(Me.txt_CTOStart)
ws8.Range("BK" & LastRow8 + 1).Value = "=IF(RC[-1]="""",""0"",IF(RC[7]<>"""",RC[7]-RC[-1],RC[-62]-RC[-1]))"
If Not Len(Me.txt_CTONext) = 0 Then ws8.Range("BL" & LastRow8 + 1).Value = CDate(Me.txt_CTONext)
If Not Len(Me.txt_CTOPymtAmt) = 0 Then ws8.Range("BM" & LastRow8 + 1).Value = CCur(Me.txt_CTOPymtAmt)
If Not Len(Me.cobo_CTOFreq) = 0 Then ws8.Range("BN" & LastRow8 + 1).Value = Me.cobo_CTOFreq
ws8.Range("BO" & LastRow8 + 1).Value = "=IF(RC[-6]=""Inactive"","""",IF(RC[-3]<>"""",RC[-3],IF(R[-1]C[-1]=""B"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+14),IF(R[-1]C[-1]=""W"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+7),IF(R[-1]C[-1]=""M"",DATE(YEAR(R[-1]C),MONTH(R[-1]C)+1,DAY(R[-1]C)))))))"
ws8.Range("BP" & LastRow8 + 1).Value = "=IF(RC[-7]=""Inactive"",0,IF(RC[-1]>RC[-67],0,IF(RC[-2]=""M"",RC[-3]/1,IF(RC[-2]=""B"",RC[-3]/2,IF(RC[-2]=""W"",RC[-3]/4,0)))))"
ws8.Range("BQ" & LastRow8 + 1).Value = "=IF(RC[-8]=""Inactive"",0,IF(RC[-68]<rc[-2],0,if(rc[10]>0,RC[-68]-RC[-2],0)))"
'ws8.Range("BR" & LastRow8 + 1).Value  Nothing mapped to this field, as End Dates aren't populated when Clients are being added.
ws8.Range("BS" & LastRow8 + 1).Value = "=RC[-59]+RC[-52]+RC[-43]+RC[-33]+RC[-23]+RC[-13]+RC[-3]"
ws8.Range("BT" & LastRow8 + 1).Value = CCur(Me.txt_AmtApplied)
ws8.Range("BU" & LastRow8 + 1).Value = 0
ws8.Range("BV" & LastRow8 + 1).Value = 0
ws8.Range("BW" & LastRow8 + 1).Value = 0
ws8.Range("BX" & LastRow8 + 1).Value = 0
ws8.Range("BY" & LastRow8 + 1).Value = "=RC[-5]+RC[-4]+RC[-3]+RC[-2]+RC[-1]"
ws8.Range("BZ" & LastRow8 + 1).Value = "=IF(R[-1]C[1]<0,ABS(R[-1]C[1]),0)"
ws8.Range("CA" & LastRow8 + 1).Value = "=IF(ISNUMBER(R[-1]C),RC[-8]-RC[-2]-RC[-1]+R[-1]C,RC[-8]-RC[-2]-RC[-1])"
ws8.Range("CB" & LastRow8 + 1).Value = "=IF(RC[-1]<=0,""Paid"",IF(OR(RC[-67]>0,RC[-60]>0,RC[-51]>0,RC[-41]>0,RC[-31]>0,RC[-21]>0,RC[-11]>0),""Late"",""Current""))"


ws3.Range("A" & LastRow3 + 1).Value = "=Today()"
ws3.Range("B" & LastRow3 + 1).Value = "=INDEX(Bios!C[1],MATCH('SUMMARIES'!C[1],Bios!C[3],0))"
ws3.Range("C" & LastRow3 + 1).Value = Me.txt_ClientID
ws3.Range("D" & LastRow3 + 1).Value = Me.txt_Name
ws3.Range("E" & LastRow3 + 1).Value = Me.txt_Nickname
With ws3.Range("F" & LastRow4 + 1)
    .Hyperlinks.Add _
    anchor:=.Offset(), _
    Address:="mailto:" & Me.txt_Email.Value, _
    TextToDisplay:=Me.txt_Email.Value
End With
ws3.Range("G" & LastRow3 + 1).Value = "=IF(OR(RC[4]>0,RC[6]>0,RC[8]>0,RC[10]>0,RC[12]>0,RC[14]>0,RC[16]>0),""Late"",""Current"")"
'ws3.Range("H" & LastRow3 + 1).FormulaArray
'ws3.Range("I" & LastRow3 + 1).FormulaArray
ws3.Range("J" & LastRow3 + 1).FormulaArray = "=MAX(IF(INDIRECT(""'""&RC[-7]&""'!D:D"")='SUMMARIES'!RC[-7],INDIRECT(""'""&RC[-7]&""'!I:I"")))"
ws3.Range("K" & LastRow3 + 1).FormulaArray = "=MAX(IF(INDIRECT(""'""&RC[-8]&""'!D:D"")='SUMMARIES'!RC[-8],INDIRECT(""'""&RC[-8]&""'!M:M"")))"
ws3.Range("L" & LastRow3 + 1).FormulaArray = "=MAX(IF(INDIRECT(""'""&RC[-9]&""'!D:D"")='SUMMARIES'!RC[-9],INDIRECT(""'""&RC[-9]&""'!P:P"")))"
ws3.Range("M" & LastRow3 + 1).FormulaArray = "=MAX(IF(INDIRECT(""'""&RC[-10]&""'!D:D"")='SUMMARIES'!RC[-10],INDIRECT(""'""&RC[-10]&""'!T:T"")))"
ws3.Range("N" & LastRow3 + 1).FormulaArray = "=MAX(IF(INDIRECT(""'""&RC[-11]&""'!D:D"")='SUMMARIES'!RC[-11],INDIRECT(""'""&RC[-11]&""'!W:W"")))"
ws3.Range("O" & LastRow3 + 1).FormulaArray = "=MAX(IF(INDIRECT(""'""&RC[-12]&""'!D:D"")='SUMMARIES'!RC[-12],INDIRECT(""'""&RC[-12]&""'!AC:AC"")))"
ws3.Range("P" & LastRow3 + 1).FormulaArray = "=MAX(IF(INDIRECT(""'""&RC[-13]&""'!D:D"")='SUMMARIES'!RC[-13],INDIRECT(""'""&RC[-13]&""'!AG:AG"")))"
ws3.Range("Q" & LastRow3 + 1).FormulaArray = "=MAX(IF(INDIRECT(""'""&RC[-14]&""'!D:D"")='SUMMARIES'!RC[-14],INDIRECT(""'""&RC[-14]&""'!AM:AM"")))"
ws3.Range("R" & LastRow3 + 1).FormulaArray = "=MAX(IF(INDIRECT(""'""&RC[-15]&""'!D:D"")='SUMMARIES'!RC[-15],INDIRECT(""'""&RC[-15]&""'!AQ:AQ"")))"
ws3.Range("S" & LastRow3 + 1).FormulaArray = "=MAX(IF(INDIRECT(""'""&RC[-16]&""'!D:D"")='SUMMARIES'!RC[-16],INDIRECT(""'""&RC[-16]&""'!AW:AW"")))"
ws3.Range("T" & LastRow3 + 1).FormulaArray = "=MAX(IF(INDIRECT(""'""&RC[-17]&""'!D:D"")='SUMMARIES'!RC[-17],INDIRECT(""'""&RC[-17]&""'!BA:BA"")))"
ws3.Range("U" & LastRow3 + 1).FormulaArray = "=MAX(IF(INDIRECT(""'""&RC[-18]&""'!D:D"")='SUMMARIES'!RC[-18],INDIRECT(""'""&RC[-18]&""'!BG:BG"")))"
ws3.Range("V" & LastRow3 + 1).FormulaArray = "=MAX(IF(INDIRECT(""'""&RC[-19]&""'!D:D"")='SUMMARIES'!RC[-19],INDIRECT(""'""&RC[-19]&""'!BK:BK"")))"
ws3.Range("W" & LastRow3 + 1).FormulaArray = "=MAX(IF(INDIRECT(""'""&RC[-20]&""'!D:D"")='SUMMARIES'!RC[-20],INDIRECT(""'""&RC[-20]&""'!BQ:BQ"")))"
ws3.Range("X" & LastRow3 + 1).FormulaArray = "=MAX(IF(Stats!C[-21]='SUMMARIES'!RC[-21],Stats!C[-15]))"
ws3.Range("Y" & LastRow3 + 1).Value = "=RC[-24]-RC[-1]"
ws3.Range("Z" & LastRow3 + 1).FormulaArray = "=MAX(IF(Stats!C[-23]='SUMMARIES'!RC[-23],Stats!C[-17]))"
ws3.Range("AA" & LastRow3 + 1).Value = "=RC[-26]-RC[-1]"
ws3.Range("AB" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-17],MATCH(MIN(IF(STATS!C[-25]=RC[-25],STATS!C[-19])),IF(STATS!C[-25]=RC[-25],STATS!C[-19]),0))"
ws3.Range("AC" & LastRow3 + 1).FormulaArray = "=MIN(IF(STATS!C[-26]='SUMMARIES'!RC[-26],STATS!C[-18]))"
ws3.Range("AD" & LastRow3 + 1).Value = "=RC[-1]-RC[-2]"
ws3.Range("AE" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-20],MATCH(MAX(IF(STATS!C[-28]=RC[-28],STATS!C[-22])),IF(STATS!C[-28]=RC[-28],STATS!C[-22]),0))"
ws3.Range("AF" & LastRow3 + 1).Value = "=RC[-1]-RC[-4]"
ws3.Range("AG" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-20],MATCH(MIN(IF(STATS!C[-30]=RC[-30],STATS!C[-24])),IF(STATS!C[-30]=RC[-30],STATS!C[-24]),0))"
ws3.Range("AH" & LastRow3 + 1).FormulaArray = "=MIN(IF(STATS!C[-31]='SUMMARIES'!RC[-31],STATS!C[-21]))"
ws3.Range("AI" & LastRow3 + 1).Value = "=RC[-1]-RC[-2]"
ws3.Range("AJ" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-23],MATCH(MAX(IF(STATS!C[-33]=RC[-33],STATS!C[-27])),IF(STATS!C[-33]=RC[-33],STATS!C[-27]),0))"
ws3.Range("AK" & LastRow3 + 1).Value = "=RC[-1]-RC[-4]"
ws3.Range("AL" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-23],MATCH(MIN(IF(STATS!C[-35]=RC[-35],STATS!C[-29])),IF(STATS!C[-35]=RC[-35],STATS!C[-29]),0))"
ws3.Range("AM" & LastRow3 + 1).FormulaArray = "=MIN(IF(STATS!C[-36]='SUMMARIES'!RC[-36],STATS!C[-24]))"
ws3.Range("AN" & LastRow3 + 1).Value = "=RC[-1]-RC[-2]"
ws3.Range("AO" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-26],MATCH(MAX(IF(STATS!C[-38]=RC[-38],STATS!C[-32])),IF(STATS!C[-38]=RC[-38],STATS!C[-32]),0))"
ws3.Range("AP" & LastRow3 + 1).Value = "=RC[-1]-RC[-4]"
ws3.Range("AQ" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-26],MATCH(MIN(IF(STATS!C[-40]=RC[-40],STATS!C[-34])),IF(STATS!C[-40]=RC[-40],STATS!C[-34]),0))"
ws3.Range("AR" & LastRow3 + 1).FormulaArray = "=MIN(IF(STATS!C[-41]='SUMMARIES'!RC[-41],STATS!C[-27]))"
ws3.Range("AS" & LastRow3 + 1).Value = "=RC[-1]-RC[-2]"
ws3.Range("AT" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-29],MATCH(MAX(IF(STATS!C[-43]=RC[-43],STATS!C[-37])),IF(STATS!C[-43]=RC[-43],STATS!C[-37]),0))"
ws3.Range("AU" & LastRow3 + 1).Value = "=RC[-1]-RC[-4]"
ws3.Range("AV" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-29],MATCH(MIN(IF(STATS!C[-45]=RC[-45],STATS!C[-39])),IF(STATS!C[-45]=RC[-45],STATS!C[-39]),0))"
ws3.Range("AW" & LastRow3 + 1).FormulaArray = "=MIN(IF(STATS!C[-46]='SUMMARIES'!RC[-46],STATS!C[-30]))"
ws3.Range("AX" & LastRow3 + 1).Value = "=RC[-1]-RC[-2]"
ws3.Range("AY" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-32],MATCH(MAX(IF(STATS!C[-48]=RC[-48],STATS!C[-42])),IF(STATS!C[-48]=RC[-48],STATS!C[-42]),0))"
ws3.Range("AZ" & LastRow3 + 1).Value = "=RC[-1]-RC[-4]"
ws3.Range("BA" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-32],MATCH(MIN(IF(STATS!C[-50]=RC[-50],STATS!C[-44])),IF(STATS!C[-50]=RC[-50],STATS!C[-44]),0))"
ws3.Range("BB" & LastRow3 + 1).FormulaArray = "=MIN(IF(STATS!C[-51]='SUMMARIES'!RC[-51],STATS!C[-33]))"
ws3.Range("BC" & LastRow3 + 1).Value = "=RC[-1]-RC[-2]"
ws3.Range("BD" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-35],MATCH(MAX(IF(STATS!C[-53]=RC[-53],STATS!C[-47])),IF(STATS!C[-53]=RC[-53],STATS!C[-47]),0))"
ws3.Range("BE" & LastRow3 + 1).Value = "=RC[-1]-RC[-4]"
ws3.Range("BF" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-35],MATCH(MIN(IF(STATS!C[-55]=RC[-55],STATS!C[-49])),IF(STATS!C[-55]=RC[-55],STATS!C[-49]),0))"
ws3.Range("BG" & LastRow3 + 1).FormulaArray = "=MIN(IF(STATS!C[-56]='SUMMARIES'!RC[-56],STATS!C[-36]))"
ws3.Range("BH" & LastRow3 + 1).Value = "=RC[-1]-RC[-2]"
ws3.Range("BI" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-38],MATCH(MAX(IF(STATS!C[-58]=RC[-58],STATS!C[-52])),IF(STATS!C[-58]=RC[-58],STATS!C[-52]),0))"
ws3.Range("BJ" & LastRow3 + 1).Value = "=RC[-1]-RC[-4]"
ws3.Range("BK" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-38],MATCH(MIN(IF(STATS!C[-60]=RC[-60],STATS!C[-54])),IF(STATS!C[-60]=RC[-60],STATS!C[-54]),0))"
ws3.Range("BL" & LastRow3 + 1).FormulaArray = "=MIN(IF(STATS!C[-61]='SUMMARIES'!RC[-61],STATS!C[-39]))"
ws3.Range("BM" & LastRow3 + 1).Value = "=RC[-1]-RC[-2]"
ws3.Range("BN" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-41],MATCH(MAX(IF(STATS!C[-63]=RC[-63],STATS!C[-57])),IF(STATS!C[-63]=RC[-63],STATS!C[-57]),0))"
ws3.Range("BO" & LastRow3 + 1).Value = "=RC[-1]-RC[-4]"
ws3.Range("BP" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-41],MATCH(MIN(IF(STATS!C[-65]=RC[-65],STATS!C[-59])),IF(STATS!C[-65]=RC[-65],STATS!C[-59]),0))"
ws3.Range("BQ" & LastRow3 + 1).FormulaArray = "=MIN(IF(STATS!C[-66]='SUMMARIES'!RC[-66],STATS!C[-42]))"
ws3.Range("BR" & LastRow3 + 1).Value = "=RC[-1]-RC[-2]"
ws3.Range("BS" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-44],MATCH(MAX(IF(STATS!C[-68]=RC[-68],STATS!C[-62])),IF(STATS!C[-68]=RC[-68],STATS!C[-62]),0))"
ws3.Range("BT" & LastRow3 + 1).Value = "=RC[-1]-RC[-4]"
ws3.Range("BU" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-44],MATCH(MIN(IF(STATS!C[-70]=RC[-70],STATS!C[-64])),IF(STATS!C[-70]=RC[-70],STATS!C[-64]),0))"
ws3.Range("BV" & LastRow3 + 1).FormulaArray = "=MIN(IF(STATS!C[-71]='SUMMARIES'!RC[-71],STATS!C[-45]))"
ws3.Range("BW" & LastRow3 + 1).Value = "=RC[-1]-RC[-2]"
ws3.Range("BX" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-47],MATCH(MAX(IF(STATS!C[-73]=RC[-73],STATS!C[-67])),IF(STATS!C[-73]=RC[-73],STATS!C[-67]),0))"
ws3.Range("BY" & LastRow3 + 1).Value = "=RC[-1]-RC[-4]"
ws3.Range("BZ" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-46],MATCH(MIN(IF(STATS!C[-75]=RC[-75],STATS!C[-69])),IF(STATS!C[-75]=RC[-75],STATS!C[-69]),0))"
ws3.Range("CA" & LastRow3 + 1).FormulaArray = "=MIN(IF(STATS!C[-76]='SUMMARIES'!RC[-76],STATS!C[-48]))"
ws3.Range("CB" & LastRow3 + 1).Value = "=RC[-1]-RC[-2]"
ws3.Range("CC" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-50],MATCH(MAX(IF(STATS!C[-78]=RC[-78],STATS!C[-72])),IF(STATS!C[-78]=RC[-78],STATS!C[-72]),0))"
ws3.Range("CD" & LastRow3 + 1).Value = "=RC[-1]-RC[-4]"
ws3.Range("CE" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-49],MATCH(MIN(IF(STATS!C[-80]=RC[-80],STATS!C[-74])),IF(STATS!C[-80]=RC[-80],STATS!C[-74]),0))"
ws3.Range("CF" & LastRow3 + 1).FormulaArray = "=MIN(IF(STATS!C[-81]='SUMMARIES'!RC[-81],STATS!C[-51]))"
ws3.Range("CG" & LastRow3 + 1).Value = "=RC[-1]-RC[-2]"
ws3.Range("CH" & LastRow3 + 1).FormulaArray = "=INDEX(STATS!C[-53],MATCH(MAX(IF(STATS!C[-83]=RC[-83],STATS!C[-77])),IF(STATS!C[-83]=RC[-83],STATS!C[-77]),0))"
ws3.Range("CI" & LastRow3 + 1).Value = "=RC[-1]-RC[-4]"


End If


ws8.Copy before:=ws9
ActiveSheet.Name = ws8.Range("D2").Value


ws8.Activate
Rows(2).EntireRow.Delete


Application.Calculation = xlCalculationAutomatic
Unload Me
MsgBox "The new Client's data has been recorded."
Application.ScreenUpdating = True
Application.Calculation = xlCalculationManual


Call AddSummaryPymtInfo


End Sub

</rc[-2],0,if(rc[10]></rc[-2],0,if(rc[20]></rc[-2],0,if(rc[30]></rc[-2],0,if(rc[40]></rc[-2],0,if(rc[50]>

 

[offthelip]

I think you will find that your workbook is always going to be very slow, the problem is INDIRECT is volatile formula which means it must be recalculated whenever anything changes anywhere within your workbook or any other workbook which is open. You would appear to have rather a lot of them.
I would suggest redesigning your system so that the equations are only put in when you know the name of the worksheet. I would do this by putting something into the wookbook newsheet event to detect the new sheet and populate your ws3 with the correct equations.
Note: I once did something similar, but rather than adding new sheets for each client, I added a new workbook for each new client linked to the master workbook, including a hyperlink to open the client workbook. There were about 60 to 70 new clients a month and after two years it was still running fast and smoothly.
This had the advantage that majority of client data remains closed and not loaded unless somebody wants to access the client, yet the summary in master gets all the values from the client workbooks

 

[reberryjr]

@offthelip that sounds like a better way to go. I'll look into that after I build this version (I've been working on it for about 4 months now...smh). For now, I think I'm just going to paste the formula values and reinsert the formulas on changes that impact them. I fully expected to get a working version, then work on optimizing it. It's further challenged by the fact that the End User has next to no computer experience. I asked her if she had Excel and she sent me a screenshot of her screen and asked, "Do you mean this thing?".

Many, many thanks for the feedback. Alternate approaches are what's helping me learn more.

 

[JonXL]

I thought about that, but I was trying to find a way to incorporate Last Row into the code. I'm not sure how to go about that though.

While it sounds like you're already planning to go another route on this, hopefully this formula might still be helpful somewhere.

This will find the last row of column A:A containing data (sort of, see below):

=MAX(IFERROR(MATCH(9.99999999999999E+307,A:A,1),0),IFERROR(MATCH(REPT("Z",255),A:A,1),0))

The limitation is that a cell with 255 or more repeating Zs in the range will 'break' the formula and cause an incorrect result. I've found this limit to be more than acceptable, though, given the improbability of such a record being entered - even accidentally.

This formula from exceljet will return the same results (sans limitations), but I find it calculates significantly slower and so might not help your speed issue:

'=LOOKUP(2,1/(A:A<>""),ROW(A:A))

https://exceljet.net/formula/get-value-of-last-non-empty-cell

 

[reberryjr]

@offthelip so, I've been thinking about your suggestion and I'm liking it more and more. I do have a couple of questions about how the theory would work, based on my needs.

Ultimately, here's what I'm looking to do:
Workability
1. Present the EU (End User) with a form to enter key data.
2. That data then maps a few different sheets, depending upon the category of the data.
3. Calculation of things such as Measurement Changes, Payment Amounts, Payment Statuses, Payment Due Dates, etc.
4. Present the EU with a Summary Sheet that compiles the critical data they would need to see and work with.
5. Have a "Management" page that houses either pivot tables and/or other mechanisms that show the EU when someone is past due for a payment, past due for sending in measurement updates, etc.

"Bells and Whistles"
1. Create some graphs that present visual progression through their training.
2. Allow the EU to email Clients, from the spreadsheet via Outlook, when they need a reminder for something, or their progression graphs.

Based on your suggestion, here's what I'm imagining:

• When I would normally create an individual sheet for the Client, I would instead create a new workbook.
• I'm thinking this would mean:
  • populating a "template" with the data from the form
  • saving the template as whatever I'm going to use to look it up during update code
  • deleting the data from the "template"

Here are some questions I have:

• For the Client specific sheets that I create, I assume that I would just have whatever code is applicable, nested into the template. That would transfer over every time the template is saved...correct?
• When updates are being made, they would be made from a "Core" spreadsheet. How difficult is it to code for that "Core" spreadsheet to read off of the Client specific sheets? (I've only been working with Excel VBA for about 4 or 5 months now...at least to this degree.)
• I would still want this "Core" spreadsheet to house the Summary and Management sheets mentioned above. I'm a little concerned about the efficiency of the Core spreadsheet reaching out to 40-50 other spreadsheets to retrieve data.

Sorry for all of the questions, but I figured I'd ask them before I start marching down an entirely different path.

 

[offthelip]

You seemed to have grasped very well the idea that I was talking about. To give you a bit more detail about my application which I can describe very easily it will hopefully help you make some decisions about how it would work with your application;
The system was designed to handle and keep a recrod of Client applications, the records needed to be kept for two financial years.
On the Master workbook there was a "New Client" button, when this was clicked rather than opening a form, I copied and opened Template workbook which was set up to look very like a form, I created a New folder for the client and renamed the template and put it in the clients folder.( The separate clients folder was a specifc requirement for this application) At this point I knew the name of the file so I could put links in the Master summary sheet to all the relevant data on the Client workbook. I actually used a hidden "Template" sheet on the master workbook which gave the mapping for these links, this meant that if the Client Workbook changed ( which it did) I could change the mapping to the Master workbook just by changing the mapping in the hidden worksheet.
I put all the calculations relevant to that client in the workbook template, So the master template was linked to the sum of the outgoings column on the client workbook rather than putting the "sum" formula in the Master worksheet.
I didn't actually need any VBA code in the client template I did it all with formula, but I don't see any reason why you couldn't populate the Client template with code as well.
The system was adding about 60 clients a month. I organised it so that it autmatically generated a new tab each month so it was easy to create monthly summaries. When I last went to the client ( to sort out a problem see below) they had been using it for over two years they over 1000 workbooks linked to the master and it was opening in about 30 seconds and recalculationg time was unnoticeable. So linking to 1000 of workbooks is fine provided thay don't change very much.
The problem I encountered was the client had problem when Excel crashed and they used the Austosaved version of the Master. EXcel in it's wisdom had replaced all the absolute links to the client workbooks with links to the temp area of the user who was using the system when Excel crashed. So I suggest turning off Autosave if you do use the linking system that I have described.
So to answer your three questions
1 , Yes that should work fine.
2: It depends what you mean by updates. The way I designed my system was to allow changes to the Client template to be accomodated very easily by changing the workbook template and the changing the hidden Mapping sheet in the Master workbook
. Changes in the Master workbook didn't effect anything
3: In my experience it shouldn't be a problem, I was linking 9 cells plus one hyperlink to each client workbook across an internal network which was not very fast, nobody using it ever raised even a query about how long it took to open or update it. Note because of the way I split up the clients there were never more than about 60 client workbooks referenced from the Active worksheet.
Best of Luck, and do ask more questions if you want.