Microsoft Excel – referencing last row in a worksheet from another worksheet using OFFSET

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.

Offset1

 

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:

=OFFSET(OrdersA1,COUNTA(Orders!$A:$A)-1,0)

I get the result 153 in cell A2 of my second worksheet.

OFFSET2

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).

OFFSET3

Now if I add a row to my initial worksheet (or remove one) the answer in Sheet 2 will change correspondingly.

OFFSET4

Find out more about our Microsoft Excel training Sydney Australia or our Microsoft Excel training London, Surrey, Middlesex or our Microsoft Excel training Dorset, Wiltshire, Hampshire UK.

About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 (or 150 Aussie dollars)for every time someone says "If only I'd known that." ....
This entry was posted in Microsoft Excel, Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013 and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s