Get the last non-empty cell in a column in Google Sheets (excluding formulas)
I have multiple Google Sheets that have several columns that are updated daily. All sheets are consolidated to a Summary page that updates when new data is entered into any of these sheets.
How do I find the last non-empty cell in each column of the Summary sheet, excluding cells that contain formulas returning a blank? For example, column A holds the date and it simply looks to see if a team leader has entered data in her sheet. If so, the date from her sheet is entered. The formula is:
=if(Cate!A709="","",Cate!A709
When I want to return the last date in the column, I try using this formula:
=INDEX(A:A,COUNTA(A:A),1).
It returns a blank because there are empty cells with formulas testing for future entries (like the "Cate" example above ... 710, 711, 712, etc.).
This works fine in Excel using =Last() ... but not so in Google Sheets.
Is there a simpler way to do this? I want the Summary sheet to look at all the other worksheets and provide a summary for each day, and I want to know what the most recent day's summary updated for my dashboard.
to find the last cell from the range you can do this:
=QUERY(A2:A; "limit 1 offset "&(COUNT(A2:A)-1))
or even for the whole last row:
=QUERY(A2:C5; "limit 1 offset "&(COUNT(A2:C5)-1))
