google-sheetsworksheet-functiongoogle-sheets-formula

Get the last non-empty cell in a column in Google Sheets

I use the following function 

=DAYS360(A2, A35)

to calculate the difference between two dates in my column. However, the column is ever expanding and I currently have to manually change 'A35' as I update my spreadsheet.

Is there a way (in Google Sheets) to find the last non-empty cell in this column and then dynamically set that parameter in the above function?

Solution

  • There may be a more eloquent way, but this is the way I came up with:

    The function to find the last populated cell in a column is:

    =INDEX( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ; ROWS( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ) )

    So if you combine it with your current function it would look like this:

    =DAYS360(A2,INDEX( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ; ROWS( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ) ))