Referring to the Last Cell

There may be times when you are putting together a workbook and you want to keep a summary on one worksheet and detail information on another. For instance, let's suppose Sheet1 is your summary worksheet, and you have detailed information for bank accounts on Sheet2. In looking at the detail information, you have dates in column A, and balances for different accounts in columns B, C, and D. Thus, the detailed information is a table that shows a running progression of bank balances on different dates.

In putting together your summary information on Sheet1, you realize that you need to reference the last figures in columns B, C, and D. These figures represent the latest balances, and thus are perfect for your summary. How do you do it? Particularly when you continue to add information to your detail worksheet over time?

Actually, there are several ways to approach the problem. (There are typically several ways to solve any Excel problem.) One way is to use the VLOOKUP function. At the point in the summary where you want the latest balance from column B of the detail (Sheet2), you would put the following formula:

=VLOOKUP(MAX(Sheet2!$A:$A),Sheet2!$A:$D,2)

To change references for the other two account balances, you would simply change the last number (2) to either 3 (for the account in column C) or 4 (for the account in column D). The function works because it looks up the maximum value in column A, which contains dates. It then looks in the data table (Sheet2!$A:$D) and finds the appropriate offset for the desired column.

This approach works fine, provided there are no dates in column A past the last balances entered. If there are, then the values returned will always be incorrect.

Another way to approach the problem is to use the INDEX function in conjunction with either COUNT or COUNTA. If the detail columns don't contain any text (even in the column headers), then you would use the COUNT function. If there is text included, then COUNTA is preferred. At the point where you want to include the last balance from column B of the detail, you would use the following formula:

=INDEX(Sheet2!B:B,COUNTA(Sheet2!B:B))

It looks into the table, determines the number of non-blank cells in column B, and then pulls the figure from that last non-blank cell. To adapt the formula for columns C and D, simply change the B references to the appropriate C or D.

Still another way to deal with the problem is to use the OFFSET function, as in the following:

=OFFSET(Sheet2!B1,COUNTA(Sheet2!B:B)-1,0)

This function returns the value of a cell offset from a base reference cell. In this case, the base cell is Sheet2!B1. The COUNTA function is used to determine how many rows to offset from the base, and the 0 specifies that the offset should be in the same column as the base reference. To change the formula for columns C and D, simply change all references to B to either C or D.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2170) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Referring to the Last Cell.