Using filter to populate cells from the bottom up.

I am using filter to populate calendar day cells from the top down, but would like to populate from the bottom up (see below), but would rather fill the cells in from the bottom up. A calendar day cell is illustrated below.

the current formula is:

=FILTER(Dates!$H$4:$H$37,Dates!$G$4:$G$37=DAY(H9),"")

Where:

Dates is a tab with the dates and tasks to be shown
- G - is the day number part, I.e. 10
- H - is the text to be displayed, i.e. April Fool’s Day
(n this tab H9 is the date value where DAY(h9) = 10

Sample day cell

excel

Formulas and Functions

m_tarler

Jun 14, 2025

I think they want that day to fill from the bottom up so building on Sergei's:

=LET(
  f, FILTER(Dates!$H$4:$H$37,Dates!$G$4:$G$37=DAY(H9),""),
  r, 5,
  c, TAKE(VSTACK(f, EXPAND("",r,1,"")),r),
  SORTBY(c, SEQUENCE(r),-1 )
)

note this will also automatically clip the list off if there are more than r (i.e. 5) rows of items found so you won't get #SPILL ERROR!

If you want to have an indication that the list was clipped you can use:

=LET(
  f, FILTER(Dates!$H$4:$H$37,Dates!$G$4:$G$37=DAY(H9),""),
  r, 5,
  c, IF(ROWS(f)>r,VSTACK(TAKE(f,r-1),"[>"&r&" found]"),TAKE(VSTACK(f,EXPAND("",r,1,"")),r)),
  SORTBY(c, SEQUENCE(r),-1 )
)

4 Replies

SergeiBaklan
Diamond Contributor
Jun 14, 2025
Or, if reverse by position

=LET( f, FILTER(Dates!$H$4:$H$37,Dates!$G$4:$G$37=DAY(H9),""), SORTBY(f, SEQUENCE(ROWS(f)),-1 ) )
- m_tarler
  Bronze Contributor
  Jun 14, 2025
  I think they want that day to fill from the bottom up so building on Sergei's:
  =LET( f, FILTER(Dates!$H$4:$H$37,Dates!$G$4:$G$37=DAY(H9),""), r, 5, c, TAKE(VSTACK(f, EXPAND("",r,1,"")),r), SORTBY(c, SEQUENCE(r),-1 ) )
  note this will also automatically clip the list off if there are more than r (i.e. 5) rows of items found so you won't get #SPILL ERROR!
  If you want to have an indication that the list was clipped you can use:
  =LET( f, FILTER(Dates!$H$4:$H$37,Dates!$G$4:$G$37=DAY(H9),""), r, 5, c, IF(ROWS(f)>r,VSTACK(TAKE(f,r-1),"[>"&r&" found]"),TAKE(VSTACK(f,EXPAND("",r,1,"")),r)), SORTBY(c, SEQUENCE(r),-1 ) )
  - Oscar_Philips
    Copper Contributor
    Jun 21, 2025
    m_tarler:
    Your first code snippet was the perfect response. Many thanks.
    I have used LET before, but never realized it could assign an array of values to a variable.
    TAKE, VSTACK, and EXPAND are new functions to me. Thank you for the introduction to them.
HansVogelaar
MVP
Jun 14, 2025
Do you mean that you would like to display the results in reverse order? If so:

=SORT(FILTER(Dates!$H$4:$H$37,Dates!$G$4:$G$37=DAY(H9),""), 1, -1)

Or do you want the results to start in H11 instead of H10?

Forum Discussion

Using filter to populate cells from the bottom up.

4 Replies

Resources