What if you want a worksheet that references the last row in another worksheet which will update itself automatically as the number of rows in the worksheet changes?
We can use the OFFSET function combined with a COUNTA function to do this. The OFFSET function is useful when dealing with a data range that may grow or shrink. It returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.
The COUNTA function counts how many non-blanks there are in a range.
If we want to use this on a different worksheet, it may well be worth naming cell A1 in the first worksheet, then you don’t need to reference the worksheet just its name.
In this example, I have named cell A1 in the Orders worksheet as OrdersA1.
On my next worksheet, I want cell A2 to display the last row of the Orders worksheet which will update automatically as the last row changes.
The syntax for the OFFSET function is:
OFFSET(reference, rows, cols, [height], [width])
Rows is the number of rows up or down from the base cell, columns is the number of columns from the base cell)
So in cell A2 on our second worksheet, we will start with:
I get the result 153 in cell A2 of my second worksheet.
This is a good start but if I want to get the whole row at once, I need to create an array formula and use height as being 1 and width as being 7.
So on Sheet 2, I first select cells A2:G2.
Then my formula is
=OFFSET(OrdersA1,COUNTA(Orders!$A:$A)-1,0,1,7) at the end of which to make it into an array formula I need to type Ctrl + Shift + Enter which puts curly brackets round the formula (you can’t just type them in).
Now if I add a row to my initial worksheet (or remove one) the answer in Sheet 2 will change correspondingly.