How to summarize dates on a cover sheet

[Space250]

Hello all, 

I currently have 2 sheets that are in the general format of:

Client Name | Upcoming Date | Type 

Both spreadsheets are within the same file and I would like to add a cover sheet that basically lists all Client Names and all upcoming dates that are "this week" (or if easier, in the next 7 days) and then output a list on the cover sheet. Ideally this list would stay up to date based on the current day so that rather than searching the other sheets, it can be looked at very easily. 

Is this possible, and if so, how would I go about making it happen?

Thanks so much! 

[#2]

@Space250 

The difficult part is in collating data from multiple sheets.  Maybe one of the experts will join the conversation.  (It may be better to combine and maintain all the data into one new worksheet, and use the function mentioned below to get the content of your two existing worksheets plus the cover sheet.)

Your request as stated does not involve summarization on dates, but filtering on dates. The key to (this part of) the solution is the FILTER function. If your data is in the range A2:C20 (and assuming your Upcoming Date data in column B is stored as dates, not as text), the cover sheet formula to retrieve the subset from one worksheet might be:

=FILTER( Sheet2!$A$2:$C$20, (Sheet2!$B$2:$B$20 >= TODAY()) * (Sheet2!$B$2:$B$20 < TODAY()+7), "" )

This is not guaranteed to work as written; I do not have Excel for Mac.

[#3]

@Space250 

You can do it with VBA.

Here's an example VBA program:

1. Open your Excel file and press ALT+F11 to open the Visual Basic Editor.

2. In the Visual Basic Editor, go to Insert -> Module.

3. In the new module, copy and paste the following code:

Sub UpdateCoverSheet()

    Dim CoverSheet As Worksheet
    Dim DataSheet1 As Worksheet
    Dim DataSheet2 As Worksheet
    Dim LastRow1 As Long
    Dim LastRow2 As Long
    Dim i As Long
    Dim j As Long
    Dim ClientName As String
    Dim UpcomingDate As Date
    Dim NextWeek As Date
    
    Set CoverSheet = ThisWorkbook.Sheets("Cover Sheet")
    Set DataSheet1 = ThisWorkbook.Sheets("Sheet1")
    Set DataSheet2 = ThisWorkbook.Sheets("Sheet2")
    LastRow1 = DataSheet1.Cells(Rows.Count, "A").End(xlUp).Row
    LastRow2 = DataSheet2.Cells(Rows.Count, "A").End(xlUp).Row
    NextWeek = Date + 7
    
    'Clear existing data in Cover Sheet
    CoverSheet.Range("A2:B" & CoverSheet.Cells(Rows.Count, "A").End(xlUp).Row).ClearContents
    
    'Loop through DataSheet1 and add data to Cover Sheet
    For i = 2 To LastRow1
        ClientName = DataSheet1.Cells(i, 1).Value
        UpcomingDate = DataSheet1.Cells(i, 2).Value
        If ClientName <> "" And UpcomingDate >= Date And UpcomingDate <= NextWeek Then
            j = CoverSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
            CoverSheet.Cells(j, "A").Value = ClientName
            CoverSheet.Cells(j, "B").Value = UpcomingDate
        End If
    Next i
    
    'Loop through DataSheet2 and add data to Cover Sheet
    For i = 2 To LastRow2
        ClientName = DataSheet2.Cells(i, 1).Value
        UpcomingDate = DataSheet2.Cells(i, 2).Value
        If ClientName <> "" And UpcomingDate >= Date And UpcomingDate <= NextWeek Then
            j = CoverSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
            CoverSheet.Cells(j, "A").Value = ClientName
            CoverSheet.Cells(j, "B").Value = UpcomingDate
        End If
    Next i
    
End Sub

1. Change the sheet names "Cover Sheet," "Sheet1," and "Sheet2" in the code to match your actual sheet names.

2. Save the module and close the Visual Basic Editor.

3. Go back to your Excel file and press ALT+F8 to open the Macro dialog box.

4. Select the "UpdateCoverSheet" macro and click the "Run" button.

5. The macro will loop through both Sheet1 and Sheet2, find all client names and upcoming dates that meet the criteria for "this week" or the next 7 days based on the current date, and add them to the Cover Sheet.

6. If you want to update the data on the Cover Sheet, simply run the macro again.

I hope this helps! 

[#4]

@Space250 

My approach is to use VSTACK but first, filtered dynamic ranges must be created:

Client1 - "Sheet1"
=LET(
    data, Sheet1!$D$2:$F$10000,
    nonblank, COUNTA(Sheet1!$D$2:$D$10000),
    dynamic, TAKE(data, nonblank),
    date, TAKE(DROP(dynamic, , 1), , 1),
    SORT(FILTER(dynamic, (date >= TODAY()) * (date <= TODAY() + 7)), {2, 1})
)

Client2 "Sheet2"
=LET(
    data, Sheet2!$D$2:$F$10000,
    nonblank, COUNTA(Sheet2!$D$2:$D$10000),
    dynamic, TAKE(data, nonblank),
    date, TAKE(DROP(dynamic, , 1), , 1),
    FILTER(dynamic, (date >= TODAY()) * (date <= TODAY() + 7))
)

"Header" - an array constant
={"Client", "Upcoming date", "Type"}

Sheet level formula:

=LET(stack,SORT(VSTACK(Client1,Client2),{2,1}),VSTACK(Header,stack))

 -