VideoPhoto

Showing posts with label cell referencing. Show all posts
Showing posts with label cell referencing. Show all posts

01 May, 2021

Reference the last cell in a column

If you need to reference the last cell value in a given column, use the following array formula:

 =INDIRECT("G"&MAX(ROW(1:1048576)*(G:G<>"")))

It returns the value held in the last filled cell in column G.

If you need to find out also what the row number of the cell keeping the value is, then this array formula can be used: 

=CELL("row",INDIRECT("G"&MAX(ROW(1:1048576)*(G:G<>""))))

You may want to get both the value and location / row number of the last cell. In such a case you can combine the above formulas into one, like in this example:

=INDIRECT("G"&MAX(ROW(1:1048576)*(G:G<>"")))&"  ==> "&"Row "&CELL("row",INDIRECT("G"&MAX(ROW(1:1048576)*(G:G<>"")))) 

Sometimes the formula can return an error value. It may happen, if any of the cells in the column displays an error. To make you aware of such an error, you can include error checking part, e.g.:

=IFERROR(INDIRECT("G"&MAX(ROW(1:1048576)*(G:G<>"")))&"  ==> "&"Row "&CELL("row",INDIRECT("G"&MAX(ROW(1:1048576)*(G:G<>"")))),"Error in the column!")

Replace G in the formulas with your actual column of interest, as needed, and keep in mind that these are all ARRAY formulas, so you need to hold simultaneously CTRL+SHIFT keys and press the ENTER key to create them.