Advanced cell selection in Excel

Question

I am new to this flavor of StackExchange, so if this belongs elsewhere, please move it; I figured this would be the best place, though.

I am making an Excel Worksheet that simply stores basic financial data in 5 columns (Check Number, Date of Transaction, Description, Profit from Transaction, and Balance After Transaction) and indefinite rows. Each worksheet represents one month, and each Workbook represents a year. As I make or receive a payment, I store it as a new row, which, inherently, makes the number of rows per month indefinite. Each transaction's Balance cell is the sum of the Balance cell of the row above it and the Profit cell of its row. I want each month to start off with a special row (first one after column headers) that displays a summary of the last month's transactions. For instance, the Balance After Transaction cell would display the last row's balance, and the Profit from Transaction cell would display the overall profits of the month)

I know that if I knew every month had exactly 100 expenses, I could achieve this for March with the following formulas for profit and balance, respectively:

=February!E2 - February!E102
=February!E102

However, I do NOT know how many rows will be in each month's table, and I'd like to automate this as much as possible (for instance, if I find a missed or duplicated expense in January, I don't want to have to update all the formulas that point to the ending January balance). How can I have Excel automatically use the last entered value in a column, in any given Excel spreadsheet, in a formula?

Answer 1

Another simple way would be to insert names into the workbook. Jan, Feb, etc. Use those to determine your last row by counting the amount of date entries in your date column.

For instance insert a name "Jan" and RefersTo =COUNT(January!A:A)+2 This will give you the row count and you can use it in your formula with indirect to build the range reference.

The last cell in the row will be =INDIRECT("E"&Jan) and you can use these references in a summary sheet elsewhere as well if you like.

This assumes numbers(dates) in column A. It will ignore a header if it is text, but in row 2, make sure to not have a numeric value or it will skew your row count.

Answer 2

Don't use indirect, with this level of calculation it will be painful. Indirect is volatile and will cause every single formula in your sheet to recalculate every time you change anything.

You can use index instead. Would look like:

=INDEX(Month!A:E,COUNTA(Month!A:A),COLUMN())

You only reference whole columns and the column offset is calculated automatically with the COLUMN() function.