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

My first suggestion will be to convert this range into a table (Insert -> Table), and then you can add a Totals row, which you can reference from a different sheet/workbook, by it's name and then there's no need to worry about number of rows.

If it's not an option, then you can do this:

To get the previous month's summary of profit you should use

=SUM(Month!$D:$D)

(this assumes that the column of profits has no other numbers besides the profits).

And for the balance of the previous month, @Jesse formula should work, but only if your table starts from the first row, and if it doesn't you can try this:

=INDEX(Month!$E:$E,MATCH(TRUE,ISBLANK(Month!$E:$E),0)-1)

when entering it, make sure to use CTRL+SHIFT+ENTER. This will find use ISBLANK on the whole column to find the first empty cell, and then retrieve the value just above it (which would be the last line).

To make this more dynamic and future-proof, I would suggest using named ranges, so if will be required, you won't have to change all the references to the columns.

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.

Answer 3

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.