A friend is setting up an Excel workbook to support some basic bookkeeping. The idea is to have one worksheet per month, with each worksheet recording financial items in a "synoptic" format.

One of the columns is a "Bank Balance" column. At the top is the balance forward from the previous month. The bottom of the column shows the new balance after the current month's transactions are added/subtracted.

It would be nice to have the balance forward amount on this month's worksheet automatically reference the bottom line number of the previous month's worksheet.

In principle this can be done using a macro function such as shown at http://exceltips.vitalnews.com/Pages/T1221_Relative_Worksheet_References.html . The example shown returns a cell address from a relative worksheet, but it could be modified to return just a relative Worksheet reference.

However, in my case there is a small problem. Each month may have different numbers of items/transactions, so the cell position that holds the new "bottom line" figure will change (different row, although its column will remain the same).

My first thought was to use a Name. However, a Name is workbook-wide; you can't have a BankBalance on the September worksheet and another BankBalance on the October one. There does not appear to be any support for worksheet-specific Names.

It turns out that the bottom line figure is literally that, the bottommost line in its column. So, it should be possible to determine the "last" row in the column that contains a value and/or formula and return its address.

This is the approach I'll work on for now, but does anyone else have a simple/elegant way to achieve what I'm trying to do? TIA.