BravoBravoAu
Board Regular
- Joined
- Nov 8, 2011
- Messages
- 63
- Office Version
- 2016
- Platform
- Windows
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:
<tbody>
</tbody>
Column D is a list of 'bank account' or 'petty cash' (for this argument, row 2 is showing current balance)
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:
<tbody>
</tbody>
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.
I currently have a WB with two WSs: data and balance.
DATA:
A | B | C | D | E | F | |
1 | DATE | CREDIT | DEBIT | ACCOUNT | BANK ACCOUNT BALANCE | PETTY CASH BALANCE |
2 | 1/7/18 | $1000 | $200 | |||
3 | 6/7/18 | $10 | bank account | |||
4 | 6/7/18 | $5 | petty cash |
<tbody>
</tbody>
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:
A | B | |
1 | ACCOUNT | AMOUNT |
2 | Bank account | |
3 | Petty Cash | |
4 | TOTAL |
<tbody>
</tbody>
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.