>>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.
>
>There's a constant somewhere, which points to the last used row or column in a range - macro could use that.
>
>Another thing would be to have a macro which would actually create a new month worksheet, assign a unique name to the total on the last used page (give it a name like BankBalance200606) and insert a reference to that total in the appropriate cell of the new page. It would still require knowing that constant - which I can't find. It's been years since the last time I needed it.
>
>Or even better - name the total cells in existing pages manually, and the macro to create the new page could automatically rely on the existence of the name on the previous sheet, and create the name of its own total cell (provided it's always at the same location on a blank page and that the page grows by inserting rows).

I thought about using unique names (specific to each Worksheet, as you've outlined) but ultimately you have to put in names manually - as you pointed out - and I'd rather avoid that if possible.

As for finding the last used row, there's a KB article: http://support.microsoft.com/kb/142526/EN-US/ . I'm about to find out how it translates to VBA. I don't know of any constant you're referring to; also I should mention my friend uses Excel97 so any cute tricks that are "later version specific" won't fly ;)