Mock Finance sheet

[Marvo]

I've set up a mock finance sheet, made it into a table. It appears to work fine, you can add a row midtable for a new transaction, you can also add at the bottom and sort by date, I can't find a fault, I just wonder whether there is a better way to do it or if any of you can see a problem with the formula for the future. I'm always looking to improve things. Maybe an UPDATE button using VBA to sort, in case of transactions having a different date? Any suggestions?

Book1
	A	B	C	D	E	F	G	H	I
1	DATE	TO	FROM	TRANSACTION	AMOUNT	BARCLAYS	NATWEST	SANTANDER	total
2	01/01/2024	Opening balance				£1,000.00	£1,000.00	£1,000.00	£3,000.00
3	02/01/2024	SANTANDER	John Smith	Bank Transfer	£500.00	£1,000.00	£1,000.00	£1,500.00	£3,500.00
4	03/01/2024	Rates	barclays	direct debit	£200.00	£800.00	£1,000.00	£1,500.00	£3,300.00
5	03/01/2024	NATWEST	BARCLAYS	Bank Transfer	£99.99	£700.01	£1,099.99	£1,500.00	£3,300.00
6	04/01/2024	santander	natwest	Bank Transfer	£350.00	£800.00	£650.00	£1,850.00	£3,300.00
7	05/01/2024	Bill Brown	SANTANDER	Standing order	£75.00	£800.00	£650.00	£1,775.00	£3,225.00
8	06/01/2024	SANTANDER	pension	Bank Transfer	£1,000.00	£800.00	£650.00	£2,775.00	£4,225.00
9	07/01/2024	ENERGY	SANTANDER	direct debit	£340.00	£800.00	£650.00	£2,435.00	£3,885.00
Sheet1

Cell Formulas
Range	Formula
I2:I9	I2	=SUM(F2:H2)
F3:F5,F7:F9	F3	=IF($B3=$F$1,F2+$E3,IF($C3=$F$1,F2-$E3,F2))
G3:G5,G7:G9	G3	=IF($B3=$G$1,G2+$E3,IF($C3=$G$1,G2-$E3,G2))
H3:H5,H7:H9	H3	=IF($B3=$H$1,H2+$E3,IF($C3=$H$1,H2-$E3,H2))
F6	F6	=IF($B6=$F$1,F4+$E6,IF($C6=$F$1,F4-$E6,F4))
G6	G6	=IF($B6=$G$1,G4+$E6,IF($C6=$G$1,G4-$E6,G4))
H6	H6	=IF($B6=$H$1,H4+$E6,IF($C6=$H$1,H4-$E6,H4))

 

[Marvo]

This did fail when put into action, added a row midtable and it didn't copy down correctly. Good job I noticed it.

Back to the drawing board.

 

[Dave Patton]

Use a Table
One formula
You can filter on bank etc,
Much of the input will auto fill as you start the data entry

T202404.xlsm
	A	B	C	D	E	F
12			Total		3,885.00
13
14	Date	Name	Transaction	Column1	Amount	Bank
15	01-Jan-24		opening amount		1,000.00	Barclays
16	01-Jan-24		opening amount		1,000.00	Natwest
17	01-Jan-24		opening amount		1,000.00	Santander
18	02-Jan-24	John Smith	Transfer		500.00	Santander
19	03-Jan-24	Rates	direct debit		-200.00	Barclays
20	03-Jan-24		bank transfer		-99.99	Barclays
21	03-Jan-24		bank transfer		99.99	Natwest
22	04-Jan-24		bank transfer		-350.00	Natwest
23	04-Jan-24		bank transfer		350.00	Santander
24	05-Jan-24		standing order		-75.00	Santander
25	06-Jan-24		pension		1,000.00	Santander
26	07-Jan-24	Energy	direct debit		-340.00	Santander
3d

Cell Formulas
Range	Formula
E12	E12	=SUBTOTAL(109,Table3[Amount])

 

[Marvo]

Thank you but I can't do that (I don't think) as the table runs to the end of the year with all future income and spending already entered. Its the day to day transactions that require adding.

It's so I can see what lies ahead and move money where appropriate.

I do have a formula that is doing the job, I thought I might be able to simplify it. For example this would be in F3

=SUM(IF($B3=F$1,IFERROR(OFFSET(F3,-1,0)+$E3,0),IF($C3=F$1,IFERROR(OFFSET(F3,-1,0)-$E3,0),OFFSET($B3,-1,0))),G$5*(ROW()=2))