Simple bank account WB

[BravoBravoAu]

Hi guys - I manage the work coffee club and each fortnight need to balance the bank account and petty cash. I'm hoping a simple WB will help me and others with transparency and simplicity.

I currently have a WB with two WSs: data and balance.

DATA:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]DATE[/TD]
[TD="align: center"]CREDIT[/TD]
[TD="align: center"]DEBIT[/TD]
[TD="align: center"]ACCOUNT[/TD]
[TD="align: center"]BANK ACCOUNT BALANCE[/TD]
[TD="align: center"]PETTY CASH BALANCE[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1/7/18[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$1000[/TD]
[TD="align: center"]$200[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]6/7/18[/TD]
[TD="align: center"]$10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]bank account[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]6/7/18[/TD]
[TD="align: center"][/TD]
[TD="align: center"]$5[/TD]
[TD="align: center"]petty cash[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Column D is a list of 'bank account' or 'petty cash' (for this argument, row 2 is showing current balance)

• based upon the $10 bank account credit, I want in E3 to show $1000 plus $10 therefore $1010.
• based upon the $5 petty cash debit, I want F4 to show $200 less $5 therefore $195.

Obviously a credit or a debit impacts positively or negatively on the balance of whichever account balance which is confusing me, plus it moves down the worksheet with each respective entry.

Lastly, I hope, the BALANCE:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]ACCOUNT[/TD]
[TD="align: center"]AMOUNT[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Bank account[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Petty Cash[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]TOTAL[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

I want B2 to display the current bank account balance ('DATA!E') and B3 to display the current petty cash balance ('DATA!F'). I'm not sure how to do this as the balance moves down the DATA worksheet.

I'm confident B4 will just be =sum(B2:B3).

Thanks as always for your assistance, legends.

 

[Michael M]

Like this ??

Excel 2007
	A	B	C	D	E	F	G
1	DATE	CREDIT	DEBIT	ACCOUNT	BANK ACCOUNT BALANCE	PETTY CASH BALANCE
2	1/07/2018				$1,000.00	$ 200.00
3	6/07/2018	$10		bank account	$1,010.00	$ 200.00
4	6/07/2018		$5	petty cash	$1,010.00	$ 195.00
5
Sheet2

Cell Formulas
Range	Formula
E3	=IF(B3>0,E2+B3,E2)
E4	=IF(B4>0,E3+B4,E3)
F3	=IF(C3>0,F2-C3,F2)
F4	=IF(C4>0,F3-C4,F3)

 

[jtakw]

Hi,

A couple of different ways.
If the Headers in E1 and F1 can be changed to match D3 and D4, the formulas can be shorter.

Book1
	A	B	C	D	E	F	G	H	I
1	DATE	CREDIT	DEBIT	ACCOUNT	BANK ACCOUNT BALANCE	PETTY CASH BALANCE
2	1/7/2018				$1,000.00	$200.00
3	6/7/2018	$10		bank account	$1,010.00			1010
4	6/7/2018		$5	petty cash		$195.00			195
5
6
7
8	ACCOUNT	AMOUNT
9	Bank account	$1,010.00
10	Petty Cash	$195.00
11	TOTAL	$1,205.00
Sheet230

Cell Formulas
Range	Formula
E3	=IF(D3="bank account",LOOKUP(9.99999999999999E+307,E$2:E2)+B3-C3,"")
F3	=IF(D3="petty cash",LOOKUP(9.99999999999999E+307,F$2:F2)+B3-C3,"")
H3	=IF(D3="bank account",E$2+SUMIF(D$3:D3,"bank account",B$3:B3)-SUMIF(D$3:D3,"bank account",C$3:C3),"")
I3	=IF(D3="petty cash",F$2+SUMIF(D$3:D3,"petty cash",B$3:B3)-SUMIF(D$3:D3,"petty cash",C$3:C3),"")
B9	=LOOKUP(9.99999999999999E+307,E2:E6)
B10	=LOOKUP(9.99999999999999E+307,F2:F6)
B11	=B9+B10

Use E3 and F3 formulas, or opt for H3 and I3 formulas.

Change/adjust cell references/range and Sheet Name according to your data.

 

[Michael M]

Sorry....and for the last bit

Excel 2007
	A	B
5
6
7	ACCOUNT	AMOUNT
8	Bank account	$ 1,010.00
9	Petty Cash	$ 195.00
10	TOTAL	$ 1,205.00
11
Sheet2

Cell Formulas
Range	Formula
B8	=LOOKUP(2,1/(1-ISBLANK(E:E)),E:E)
B9	=LOOKUP(2,1/(1-ISBLANK(F:F)),F:F)
B10	=B9+B8

 

[jtakw]

Here're the same formulas as my Post # 3, with the Headers in E1 and F1 changed:

Book1
	A	B	C	D	E	F	G	H	I
1	DATE	CREDIT	DEBIT	ACCOUNT	BANK ACCOUNT	PETTY CASH
2	1/7/2018				$1,000.00	$200.00
3	6/7/2018	$10		bank account	$1,010.00			1010
4	6/7/2018		$5	petty cash		$195.00			195
5
6
7
8	ACCOUNT	AMOUNT
9	Bank account	$1,010.00
10	Petty Cash	$195.00
11	TOTAL	$1,205.00
Sheet230 (2)

Cell Formulas
Range	Formula
E3	=IF($D3=E$1,LOOKUP(9.99999999999999E+307,E$2:E2)+$B3-$C3,"")
H3	=IF($D3=E$1,E$2+SUMIF($D$3:$D3,E$1,$B$3:$B3)-SUMIF($D$3:$D3,E$1,$C$3:$C3),"")
B9	=LOOKUP(9.99999999999999E+307,E2:E6)
B10	=LOOKUP(9.99999999999999E+307,F2:F6)
B11	=B9+B10

E3 or H3 formula copied down and across to Column F

 

[BravoBravoAu]

Thanks @jtakw and @Michael M for your great solutions.

 

[jtakw]

You're welcome.