Newbie help for a good cause, please

[jeremy466clark]

Hi there. I'm a relative newbie to Excel, and new to this forum, so forgive me if what I'm asking is easy for others. The more straight forward the answers, the easier for me please!!

I am doing voluntary work for a local food aid charity. We need to monitor every client who calls individually, whether they are first time clients (new) or return clients (existing), the number of "heads" in their household (adults, children, babies & pets), and the date of their visit. This will be recorded on Sheet 1 below. Straight forward enough so far.

I then need to summarise all of those numbers per day, per week, and per month, which I'm proposing on Sheet 2 below, and ideally, I want to fully automate this from the data entered on Sheet 1, and that's what I don't know how to do.

So what I need is.......If Column B on Sheet 1 shows N (for new), the figures in Columns C-H on Sheet 1, should be copied to Columns B-G on Sheet 2 under the correct date, based on the date entered in columns J onwards on Sheet 1. If Column B on Sheet 1 shows E (existing client), I need the figures in C-H on Sheet 1 copied to J-O on Sheet 2, again under the correct date, based on the date entered in columns J onwards on Sheet 1.

Just to complicate things further, the first time a client visits, they will obviously be shown as "N" for new, so I'd want their numbers copied to Columns B-G on Sheet 2, but if they call subsequently, they will become Existing (E) clients so any subsequent totals would need to be copied to J-O on Sheet 2.

I've tried to explain things as easily as possible. Hopefully, someone will be able to help

Thanks in advance

Jeremy

 

[mumps]

It seems that it's only carrying over data from one occurence of every date? For any date in 2023, there's only one client showing and their associated Household Heads on the Weekly Client Numbers tab.

I'm not sure what you mean. Could you please explain in detail using a few examples from your data? Also explain in detail what your expected result should look like. If you could post a manually created sample sheet showing the expected result with only 2 or 3 clients, I could compare that to the most recent workbook you posted and examine the difference.

 

[jeremy466clark]

I'm not sure what you mean. Could you please explain in detail using a few examples from your data? Also explain in detail what your expected result should look like. If you could post a manually created sample sheet showing the expected result with only 2 or 3 clients, I could compare that to the most recent workbook you posted and examine the difference.

Hi Mumps. Sorry, I'm just going out until later this evening so I've not got time at the moment to do a sample sheet.

If I explain a bit better -

If you look at the most recent file I sent you a link to, you'll see on the All Clients & Attendance tab, I've entered all the clients, their headcounts in Columns P to U, then all their individual attendance dates in Columns AH to BB. All of those attendances & headcounts should be transfered over to the Weekly Client Numbers tab, in either the New section (Columns C to H) or Existing section (Columns K to P).

So, taking the 13th November 2023 as an example, there should be well over 100 clients showing on the Weekly Client Numbers tab (sorry, I've not got time to manually count them all at the moment) & all the associated headcount numbers linked to those attendances.

In summary, I need to be able to see how many total clients (New or Existing) called, & their associated total headcounts, for each individual date.

Hope that explains better

If you need any more info, let me know & I'll pick it up later.

Cheers

Jeremy

 

[mumps]

Are you saying that you want a running total in columns C, I, K and Q in the Weekly Client Numbers sheet or do you want running totals in all columns in C:I and K:Q?

 

[jeremy466clark]

Are you saying that you want a running total in columns C, I, K and Q in the Weekly Client Numbers sheet or do you want running totals in all columns in C:I and K:Q?

Running totals please in C to I & K to Q in the Weekly Clients Numbers tab. The totals in Rows 2 & 3, as well as the weekly totals in rows 14,22,31,39 & all the other rows where I've set up "Weekly Totals" as well as the monthly totals in rows 27,64, 100 & onwards will all update from formulas I've already inserted

As an example, on the All Clients & Attendance tab, if the only client entered was row 9 (Hannah Abba), the Weekly Client Numbers Tab would show 1 new client under 13/11/23 (Cell C7) (from the first time that date was entered in AH9 on the All Clients & Attendance Tab) & 1 Adult seen (Cell D7). Cells K7 & L7 would show 2 clients & 2 adults seen from when the 2 subsequent entries of 13/11/23 were entered in cells AI9 & AJ9.

If the next client entered was Kayleigh Bell, as in Row 21 on the All Clients tab, there would be 1 New client showing under 16/11/23 on the Client Numbers Tab (Cell C10), with 2 adults & 2 dogs (D10 & G10), then Existing Clients under 11/12/23 (K40) & 23/12/23 (K53) with 2 adults & 2 dogs under the same 11/12 (L40 & O40) & 23/12 (L53 & O53) dates.

Sorry if that seems complicated. In summary, all I want to do is enter a client's details the first time they call & the date of the first visit on the All Clients tab. All those details will automatically transfer to the correct date on the Weekly Numbers Tab under the New columns. As & when they visit in future, I want to add the subsequent dates in on the All Clients tab & those figures will transfer to the Weekly Numbers tab under the Existing Client Columns. And I want totals to update automatically in the cells on the Weekly Numbers tab. The aim is to be able to look at any date and say we saw X number of new clients, feeding X number of adults, children, babies, dogs & cats, and be able to see the same data for existing clients too. The formulas I've then already entered for totals will enable me to say we fed X new & X existing clients in any week or month too & all the associated Household Heads numbers too.

Hope that explains things.

Thanks again

Jeremy

 

[mumps]

I'm not sure if I understood correctly but click here for your file. Try it out and see how it works for you. THis is the revised code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Dim lCol As Long, sCol As String, strDate As String, foundDate As Range, lRow As Long, desWS As Worksheet, fnd As Range, sCol2 As String
    Dim rng As Range, x As Long: x = 3
    Set desWS = Sheets("Weekly Client Numbers")
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lCol = Cells(5, Columns.Count).End(xlToLeft).Column
    sCol = Replace(Cells(5, lCol).Address(False, False), "5", "")
    Set fnd = Rows(4).Find("Attendance Dates")
    sCol2 = Replace(Cells(4, fnd.Column).Address(False, False), "4", "")
    If Intersect(Target, Range(sCol2 & "6:" & sCol & lRow)) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target <> "" Then
        strDate = Target
        Select Case Target.Column
            Case Is = fnd.Column
                Set foundDate = Sheets("Weekly Client Numbers").Range("B:B").Find(what:=DateValue(strDate), LookIn:=xlFormulas)
                If Not foundDate Is Nothing Then
                    For Each rng In Range("P" & Target.Row).Resize(, 6)
                        desWS.Cells(foundDate.Row, x) = desWS.Cells(foundDate.Row, x) + rng
                        x = x + 1
                    Next rng
                    Target.Offset(, -1) = "N"
                    Target.Offset(, -2) = WorksheetFunction.CountA(Range(fnd.Address).Offset(Target.Row - fnd.Row).Resize(, lCol - fnd.Column + 1))
                Else
                    MsgBox (Target & " not found.")
                    Exit Sub
                End If
            Case Is > fnd.Column
                Set foundDate = Sheets("Weekly Client Numbers").Range("B:B").Find(what:=DateValue(strDate), LookIn:=xlFormulas)
                If Not foundDate Is Nothing Then
                    x = 11
                    For Each rng In Range("P" & Target.Row).Resize(, 6)
                        desWS.Cells(foundDate.Row, x) = desWS.Cells(foundDate.Row, x) + rng
                        x = x + 1
                    Next rng
                    Target.Offset(, -(Target.Column - fnd.Column + 1)) = "E"
                    Target.Offset(, -(Target.Column - fnd.Column + 2)) = WorksheetFunction.CountA(Range(fnd.Address).Offset(Target.Row - fnd.Row).Resize(, lCol - fnd.Column + 1))
                Else
                    MsgBox (Target & " not found.")
                    Exit Sub
                End If
        End Select
        Target.Offset(, -(Target.Column - fnd.Column + 1)).Select
    Else
        Select Case Target.Column
            Case Is = fnd.Column
                Set foundDate = Sheets("Weekly Client Numbers").Range("B:B").Find(what:=DateValue(Range("A2")), LookIn:=xlFormulas)
                If Not foundDate Is Nothing Then
                    desWS.Range("C" & foundDate.Row).Resize(, 6).ClearContents
                    Range("P" & Target.Row).Resize(, 6).ClearContents
                    Target.Offset(, -1).ClearContents
                    Target.Offset(, -2).ClearContents
                Else
                    MsgBox (Target & " not found.")
                    Exit Sub
                End If
            Case Is > fnd.Column
                Set foundDate = Sheets("Weekly Client Numbers").Range("B:B").Find(what:=DateValue(Range("A2")), LookIn:=xlFormulas)
                If Not foundDate Is Nothing Then
                    If Target.Column = fnd.Column + 1 Then
                        Range("P" & Target.Row).Resize(, 6).ClearContents
                        Target.Offset(, -(Target.Column - fnd.Column + 1)) = "N"
                        Target.Offset(, -(Target.Column - fnd.Column + 2)) = 1
                    Else
                        x = 11
                        Target.Offset(, -(Target.Column - fnd.Column + 2)) = Target.Offset(, -(Target.Column - fnd.Column + 2)) - 1
                        For Each rng In Range("P" & Target.Row).Resize(, 6)
                            desWS.Cells(foundDate.Row, x) = desWS.Cells(foundDate.Row, x) - rng
                            x = x + 1
                        Next rng
                    End If
                Else
                    MsgBox (Target & " not found.")
                    Exit Sub
                End If
        End Select
        Target.Offset(, -(Target.Column - fnd.Column + 1)).Select
    End If
    Range("A2").ClearContents
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[jeremy466clark]

I'm not sure if I understood correctly but click here for your file. Try it out and see how it works for you. THis is the revised code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Dim lCol As Long, sCol As String, strDate As String, foundDate As Range, lRow As Long, desWS As Worksheet, fnd As Range, sCol2 As String
    Dim rng As Range, x As Long: x = 3
    Set desWS = Sheets("Weekly Client Numbers")
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lCol = Cells(5, Columns.Count).End(xlToLeft).Column
    sCol = Replace(Cells(5, lCol).Address(False, False), "5", "")
    Set fnd = Rows(4).Find("Attendance Dates")
    sCol2 = Replace(Cells(4, fnd.Column).Address(False, False), "4", "")
    If Intersect(Target, Range(sCol2 & "6:" & sCol & lRow)) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target <> "" Then
        strDate = Target
        Select Case Target.Column
            Case Is = fnd.Column
                Set foundDate = Sheets("Weekly Client Numbers").Range("B:B").Find(what:=DateValue(strDate), LookIn:=xlFormulas)
                If Not foundDate Is Nothing Then
                    For Each rng In Range("P" & Target.Row).Resize(, 6)
                        desWS.Cells(foundDate.Row, x) = desWS.Cells(foundDate.Row, x) + rng
                        x = x + 1
                    Next rng
                    Target.Offset(, -1) = "N"
                    Target.Offset(, -2) = WorksheetFunction.CountA(Range(fnd.Address).Offset(Target.Row - fnd.Row).Resize(, lCol - fnd.Column + 1))
                Else
                    MsgBox (Target & " not found.")
                    Exit Sub
                End If
            Case Is > fnd.Column
                Set foundDate = Sheets("Weekly Client Numbers").Range("B:B").Find(what:=DateValue(strDate), LookIn:=xlFormulas)
                If Not foundDate Is Nothing Then
                    x = 11
                    For Each rng In Range("P" & Target.Row).Resize(, 6)
                        desWS.Cells(foundDate.Row, x) = desWS.Cells(foundDate.Row, x) + rng
                        x = x + 1
                    Next rng
                    Target.Offset(, -(Target.Column - fnd.Column + 1)) = "E"
                    Target.Offset(, -(Target.Column - fnd.Column + 2)) = WorksheetFunction.CountA(Range(fnd.Address).Offset(Target.Row - fnd.Row).Resize(, lCol - fnd.Column + 1))
                Else
                    MsgBox (Target & " not found.")
                    Exit Sub
                End If
        End Select
        Target.Offset(, -(Target.Column - fnd.Column + 1)).Select
    Else
        Select Case Target.Column
            Case Is = fnd.Column
                Set foundDate = Sheets("Weekly Client Numbers").Range("B:B").Find(what:=DateValue(Range("A2")), LookIn:=xlFormulas)
                If Not foundDate Is Nothing Then
                    desWS.Range("C" & foundDate.Row).Resize(, 6).ClearContents
                    Range("P" & Target.Row).Resize(, 6).ClearContents
                    Target.Offset(, -1).ClearContents
                    Target.Offset(, -2).ClearContents
                Else
                    MsgBox (Target & " not found.")
                    Exit Sub
                End If
            Case Is > fnd.Column
                Set foundDate = Sheets("Weekly Client Numbers").Range("B:B").Find(what:=DateValue(Range("A2")), LookIn:=xlFormulas)
                If Not foundDate Is Nothing Then
                    If Target.Column = fnd.Column + 1 Then
                        Range("P" & Target.Row).Resize(, 6).ClearContents
                        Target.Offset(, -(Target.Column - fnd.Column + 1)) = "N"
                        Target.Offset(, -(Target.Column - fnd.Column + 2)) = 1
                    Else
                        x = 11
                        Target.Offset(, -(Target.Column - fnd.Column + 2)) = Target.Offset(, -(Target.Column - fnd.Column + 2)) - 1
                        For Each rng In Range("P" & Target.Row).Resize(, 6)
                            desWS.Cells(foundDate.Row, x) = desWS.Cells(foundDate.Row, x) - rng
                            x = x + 1
                        Next rng
                    End If
                Else
                    MsgBox (Target & " not found.")
                    Exit Sub
                End If
        End Select
        Target.Offset(, -(Target.Column - fnd.Column + 1)).Select
    End If
    Range("A2").ClearContents
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Hi Mumps

Sorry, things still don't seem right. I've now uploaded a file with just a few names & data, & I've manually entered the totals as they should show on the Weekly Client Numbers (WCN) tab.

On the All Clients & Attendance (ACA) tab, any date ever entered in Column AH will always be a client's first visit, so all the numbers showing under Household Members (Columns P to U) I want to automatically be transfered to the WCN tab to show under the correct dates in the corresponding columns C to H (These are all the household numbers for NEW clients). So if 3 New clients attended on the same date, Columns C to H would be a total of their household numbers under the correct date.

Any subsequent visits by the same client, they become "Existing" and attendance dates will be entered in columns AI onwards on the ACA tab, so the Household Members figures in Columns P to U should then show under the correct dates but in the corresponding columns K to P on the WCN tab (These are all the household numbers for EXISTING clients). So if 3 Existing clients attended on the same date, columns K to P would be a total of their household numbers under the correct date.

Again, in summary, the idea is to be able to look at any date on the WCN tab & be able to see how many New clients attended that day, how many Existing clients attended, & how many associated household heads for those clients were helped.

Hope that clears things up

When you work out the code and send the file back to me, can you hopefully use the file that I'd already sent yesterday with all the clients entered, so I don't have to re-enter all their details?

Thanks again

Jeremy

Manual Sample Sheet.xlsm

1drv.ms

 

[mumps]

I've tested the macro on both yesterday's file and today's file and it seemed to be working exactly as you described so I'm still not sure what I'm missing. Also, I'm not sure if you tested deleting any dates but if you do the numbers in the WCN sheet are adjusted as well as the Total Visits in the ACA sheet. I will be out of town until Thursday of next week so I will not be able to respond starting tomorrow.

 

[jeremy466clark]

I've tested the macro on both yesterday's file and today's file and it seemed to be working exactly as you described so I'm still not sure what I'm missing. Also, I'm not sure if you tested deleting any dates but if you do the numbers in the WCN sheet are adjusted as well as the Total Visits in the ACA sheet. I will be out of town until Thursday of next week so I will not be able to respond starting tomorrow.

Mumps, you are & have been brilliant.

Everything appears to be working now. I just wasn't aware that I'd need to delete all the dates I'd already entered & re-enter it all.

Once again, I can't thank you enough

All the best

Jeremy

 

[mumps]

Glad it worked out.