Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
these answers may solve your problem. stackoverflow.com/questions/6301665/… –  wilson Feb 3 '12 at 7:12
    
Do you have a summary cell of the previous month? For instance, a row that sums up this data for you. It'd be easy to write a formula in that cell and then link that cell for the start of a new month. Ideally, you'd want a monthly summary at the end of the month anyway, if you want it at the beginning of the next month. As you insert or delete rows it would continue to be dynamic in both cases. –  Raystafarian Feb 3 '12 at 11:33
    
How would I do that so the summary row automatically shifts to the end? Is that possible? –  Supuhstar Feb 3 '12 at 15:33
    
also, see the latest edit, in case it clears anything up –  Supuhstar Feb 3 '12 at 15:43
    
@wilson any time I input those formulas, I get either a #NAME? error or a popup saying that "The formula you typed contains an error" –  Supuhstar Feb 3 '12 at 15:49

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer
    
Thank you! It's been a while since I asked this, but I suppose it's better late than never :3 –  Supuhstar Oct 14 '12 at 19:24

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.