Linming to the last balance on a spreadsheet

[Helen]

I have an account where the balance is changing, but I want to summarise all the accounts by itpicking up the most recent balance. At the moment I am having to keep going int and changig the cell reference.

Is there any formula that I was use for it to change the cell reference when new transactions appear and crate a new balance on other lines?

Thanks for your help
Helen

[Chris Davison]

Microsoft Excel - helen.xls_______________Running: xl97 : OS = Windows (32-bit) 4.90
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
F4		=	=INDIRECT("C"&MATCH(999999999,$C:$C))
*	A	B	C	D	E	F
1	Debit	Credit	Balance	*	*	*
2	*	50	:alert('=IF(AND(NOT(A2),NOT(B2)),"",-SUM($A$2:A2)+SUM($B$2:B2))')>50	*	*	*
3	30	*	:alert('=IF(AND(NOT(A3),NOT(B3)),"",-SUM($A$2:A3)+SUM($B$2:B3))')>20	*	*	*
4	*	100	:alert('=IF(AND(NOT(A4),NOT(B4)),"",-SUM($A$2:A4)+SUM($B$2:B4))')>120	*	Last balance	:alert('=INDIRECT("C"&MATCH(999999999,$C:$C))')>20
5	100	*	:alert('=IF(AND(NOT(A5),NOT(B5)),"",-SUM($A$2:A5)+SUM($B$2:B5))')>20	*	*	*
6	*	*	:alert('=IF(AND(NOT(A6),NOT(B6)),"",-SUM($A$2:A6)+SUM($B$2:B6))')>*	*	*	*
7	*	*	:alert('=IF(AND(NOT(A7),NOT(B7)),"",-SUM($A$2:A7)+SUM($B$2:B7))')>*	*	*	*
8	*	*	:alert('=IF(AND(NOT(A8),NOT(B8)),"",-SUM($A$2:A8)+SUM($B$2:B8))')>*	*	*	*
9	*	*	:alert('=IF(AND(NOT(A9),NOT(B9)),"",-SUM($A$2:A9)+SUM($B$2:B9))')>*	*	*	*
10	*	*	:alert('=IF(AND(NOT(A10),NOT(B10)),"",-SUM($A$2:A10)+SUM($B$2:B10))')>*	*	*	*
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.20]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


here's my effort..... it should get you started until something better comes along

[Aladin Akyurek]

here's my effort..... it should get you started until something better comes along


I wasn't sure I understood Helen's query. As it is one about balances, you'd know .

Given your example, What about entering in C2

=SUM(-A2,B2,C1)

and copying down?

The last balance would be:

=INDIRECT("C",MATCH(9.99999999999999E+307,C:C))

or

=OFFSET(C1,MATCH(9.99999999999999E+307,C:C)-1,0,1,1)

Credit & blame both should be accorded to Chris, the foregoing is just a brush over.

[Chris Davison]

*chuckling at "blame"*

Aladin, I was remembering an earlier post where we weren't showing any balance figures if the row hadn't been used, so tried to build it in using the AND and NOTs

(I must look at OFFSET more often though, thanks !)

Chris