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 is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your sheets. Alternately, you could upload a copy of your file (de-sensitized if necessary) 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. For clarification: Jon is listed as new and I assume his first visit was 01/01/2024. He then had 3 more visits. Should the "N" in column B not have been changed to "E" on his second visit?

 

[jeremy466clark]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your sheets. Alternately, you could upload a copy of your file (de-sensitized if necessary) 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. For clarification: Jon is listed as new and I assume his first visit was 01/01/2024. He then had 3 more visits. Should the "N" in column B not have been changed to "E" on his second visit?

Yes, you're absolutely right. Jon should have been changed to E on his 2nd visit. Sorry for that!!!

 

[mumps]

Can you please post the screen shots or upload your file? This will make it easier to test possible solutions.

 

[jeremy466clark]

Can you please post the screen shots or upload your file? This will make it easier to test possible solutions.

Here's a link to Dropbox. Nothing is desensitised as I've just made up the data at this stage. Obviously, additional clients will be continually added. Thanks or your help so far

Tryout.xlsx

Shared with Dropbox

www.dropbox.com

 

[mumps]

Click here to download your file. Enter the data from column A to column H. The Total Heads will be calculated automatically so you don't have to enter it. Since this would be a first visit, click in column K and a calendar will pop up for you to select a date. Once you do that, you will see the Total Heads and the appropriate data will be copied to Sheet2. You can keep adding visitors as needed. The next time an existing visitor comes in, simply go to column K for that person and select a date. The "N" in column B will automatically change to "E". You can expand Sheet1 to add more columns for dates and the macro will automatically adjust accordingly. To view the code, do the following: right click the tab name for your Sheet1 and click 'View Code'. Close the code window to return to your sheet when done.
This is the 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
    Set desWS = Sheets("Sheet2")
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    sCol = Replace(Cells(1, lCol).Address(False, False), "1", "")
    If Intersect(Target, Range("J2:" & sCol & lRow)) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    strdate = Target
    Select Case Target.Column
        Case Is = 10
            Set foundDate = Sheets("Sheet2").Range("A:A").Find(what:=DateValue(strdate), LookIn:=xlFormulas)
            If Not foundDate Is Nothing Then
                desWS.Range("B" & foundDate.Row).Resize(, 6).Value = Range("C" & Target.Row).Resize(, 6).Value
                Range("I" & Target.Row).Formula = "=sum(D" & Target.Row & ":H" & Target.Row & ")"
            Else
                MsgBox (Target & " not found.")
                Exit Sub
            End If
        Case Is > 10
            Set foundDate = Sheets("Sheet2").Range("A:A").Find(what:=DateValue(strdate), LookIn:=xlFormulas)
            If Not foundDate Is Nothing Then
                desWS.Range("J" & foundDate.Row).Resize(, 6).Value = Range("C" & Target.Row).Resize(, 6).Value
                Range("B" & Target.Row) = "E"
            Else
                MsgBox (Target & " not found.")
                Exit Sub
            End If
    End Select
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim lCol As Long, sCol As String, lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    sCol = Replace(Cells(1, lCol).Address(False, False), "1", "")
    If Intersect(Target, Range("J2:" & sCol & lRow)) Is Nothing Then Exit Sub
    CalendarFrm.Show
    Application.ScreenUpdating = True
End Sub

 

[jeremy466clark]

Click here to download your file. Enter the data from column A to column H. The Total Heads will be calculated automatically so you don't have to enter it. Since this would be a first visit, click in column K and a calendar will pop up for you to select a date. Once you do that, you will see the Total Heads and the appropriate data will be copied to Sheet2. You can keep adding visitors as needed. The next time an existing visitor comes in, simply go to column K for that person and select a date. The "N" in column B will automatically change to "E". You can expand Sheet1 to add more columns for dates and the macro will automatically adjust accordingly. To view the code, do the following: right click the tab name for your Sheet1 and click 'View Code'. Close the code window to return to your sheet when done.
This is the 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
    Set desWS = Sheets("Sheet2")
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    sCol = Replace(Cells(1, lCol).Address(False, False), "1", "")
    If Intersect(Target, Range("J2:" & sCol & lRow)) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    strdate = Target
    Select Case Target.Column
        Case Is = 10
            Set foundDate = Sheets("Sheet2").Range("A:A").Find(what:=DateValue(strdate), LookIn:=xlFormulas)
            If Not foundDate Is Nothing Then
                desWS.Range("B" & foundDate.Row).Resize(, 6).Value = Range("C" & Target.Row).Resize(, 6).Value
                Range("I" & Target.Row).Formula = "=sum(D" & Target.Row & ":H" & Target.Row & ")"
            Else
                MsgBox (Target & " not found.")
                Exit Sub
            End If
        Case Is > 10
            Set foundDate = Sheets("Sheet2").Range("A:A").Find(what:=DateValue(strdate), LookIn:=xlFormulas)
            If Not foundDate Is Nothing Then
                desWS.Range("J" & foundDate.Row).Resize(, 6).Value = Range("C" & Target.Row).Resize(, 6).Value
                Range("B" & Target.Row) = "E"
            Else
                MsgBox (Target & " not found.")
                Exit Sub
            End If
    End Select
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim lCol As Long, sCol As String, lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    sCol = Replace(Cells(1, lCol).Address(False, False), "1", "")
    If Intersect(Target, Range("J2:" & sCol & lRow)) Is Nothing Then Exit Sub
    CalendarFrm.Show
    Application.ScreenUpdating = True
End Sub

Hi again Mumps. Thank you so much. It works like a dream.
Is there any way that your coding / macros could make it so that on Sheet 1, I could potentially add additional columns as and when they are needed for additional monitoring, anywhere from Column A upto J, but the transfer of figures still transfers to Sheet 2? For instance, some of the committee are suggesting adding Addresses for clients so we can monitor whereabouts numbers are coming from, & I'll also need to add separate columns for First Name & Surnames, to give just 2 examples. I definitely won't need any other figures transfering to Sheet 2 though. Also, I'm assuming that when I add the rest of the year to Sheet 2, the figures will still transfer to the correct date from Sheet 1? Finally, I'll be looking to add "Filter" buttons to the columns, so we can sort different columns either alphabetically or numerically. Obviously, when changing, say a surname column to alphabetical, I'd need all of the client's visit dates moving with the client. I already know how to add the filter buttons, but am wondering, again, if this would muck up the macros / coding

 

[mumps]

Please upload a revised file with the additional columns. Unfortunately, if you keep adding columns, the macro would have to be changed. It is best if you could decide on a final version and post a copy of that. If you add the rest of the year, the macro will still work. With regards to the filtering, you can use the filter data feature which is built in to Excel. I’ll wait for the updated file.

 

[jeremy466clark]

Please upload a revised file with the additional columns. Unfortunately, if you keep adding columns, the macro would have to be changed. It is best if you could decide on a final version and post a copy of that. If you add the rest of the year, the macro will still work. With regards to the filtering, you can use the filter data feature which is built in to Excel. I’ll wait for the updated file.

Thanks again, Mumps. You're a star.
Here's the link for the full spreadsheet. I've done my best!!

OneDrive

Sign in to your OneDrive cloud storage and Office Online.

1drv.ms

On the "All Clients & Attendance" tab, I'll want the ability to go beyond Row 393 if client numbers need it, and beyond Column BA to add additional attendance dates if needed.
The figures entered from Columns P-U are what need to be transfered to the Weekly Client Numbers tab, columns C-H or K-P dependent whether they're new or existing clients, which will be shown in column AF on the All Clients tab.
All the Total boxes on both tabs should already have formulas in, & as you can see, I've already added Filter buttons.
Hope that's all OK.
Look forward to hearing from you.
Cheers
Jeremy

 

[jeremy466clark]

Thanks again, Mumps. You're a star.
Here's the link for the full spreadsheet. I've done my best!!

OneDrive

Sign in to your OneDrive cloud storage and Office Online.

1drv.ms

On the "All Clients & Attendance" tab, I'll want the ability to go beyond Row 393 if client numbers need it, and beyond Column BA to add additional attendance dates if needed.
The figures entered from Columns P-U are what need to be transfered to the Weekly Client Numbers tab, columns C-H or K-P dependent whether they're new or existing clients, which will be shown in column AF on the All Clients tab.
All the Total boxes on both tabs should already have formulas in, & as you can see, I've already added Filter buttons.
Hope that's all OK.
Look forward to hearing from you.
Cheers
Jeremy

Sorry to be a pain, Mumps
I've added another column at AF on the All Clients tab. Is it possible to make this column show a total count automatically for each client as their dates of attendance are entered in AH onwards, so that we can easily see how many times each client is using the service? Here's the new link -

FFF Test Spreadsheet.xlsx

1drv.ms

Thanks again
Jeremy