Is there an easy way to reference the bottom row of column if there are gaps in the data?

Options

We are after a way to return the most recent/bottom entry of a column, however the column will often have gaps of blanks.

I have got sort of close with:

=INDEX(Change:Change, COUNTIFS(Change:Change, <>""))

However any gaps you have make it look at the cell(s) above the bottom.


Is there a way to get a IF NOT BLANK type function to COUNTIFS that would allow the formula handle the blanks in the column?


Thanks

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @GrahamR

    The way I would do this is to add the System Column of Created (Date) so I have something automatic to work off of to find the most recent entry. Then I'd use an INDEX(MATCH to find the oldest date in that Created column, or the MAX date, and return the data in the "Change" column for that row.

    Try this:

    =INDEX(Change:Change, MATCH(MAX([Date Created]:[Date Created]), [Date Created]:[Date Created]))

    Keep in mind that if you created a row, but then moved the placement of that row, the formula would be basing the data off of that Date Created and not location within the sheet. Do you think this would work for you?

    Cheers!

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!