Feb 21 2023 01:30 PM
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!
Feb 28 2023 09:57 AM
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.
Feb 28 2023 10:57 AM
You can do it with VBA.
Here's an example VBA program:
Open your Excel file and press ALT+F11 to open the Visual Basic Editor.
In the Visual Basic Editor, go to Insert -> Module.
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
Change the sheet names "Cover Sheet," "Sheet1," and "Sheet2" in the code to match your actual sheet names.
Save the module and close the Visual Basic Editor.
Go back to your Excel file and press ALT+F8 to open the Macro dialog box.
Select the "UpdateCoverSheet" macro and click the "Run" button.
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.
If you want to update the data on the Cover Sheet, simply run the macro again.
I hope this helps!
Feb 28 2023 12:29 PM - edited Feb 28 2023 01:03 PM
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))
-