Forum Discussion

Space250's avatar
Space250
Copper Contributor
Feb 21, 2023

How to summarize dates on a cover sheet

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 she...
excel
Excel on Mac
Formulas and Functions
Patrick2788's avatar
Patrick2788
Silver Contributor
Feb 28, 2023

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))

 

 -  

Resources