Hello There,
I have a defined a Cash Book table and a setup table which handles petty cash of various departments.
I need help in constructing a formula for deriving the closing balance. This is based on some criteria as described below. Also, attached a sample workbook with the description for your reference.
Case:
There are various divisions which contributes to the daily organizational expenses.
Expenses which are paid only by Division 'Third Division', needs to be paid back to them.
There is a Setup table Table2. This has the division names where the amount needs to be paid back to that Division. All those Divisions would be listed here. This will be reflected in Table1 column Closing Balance.
Requirement in Table1:
The column 'Closing Balance' should be calculated as follows:
1. Closing Balance= Previous rows 'Closing Balance' + current row's 'Begin Balance' - current rows 'Expense Amount' (**). Any new 'Begin balance' will be added to the 'Closing Balance'
2. (**) If column 'Type' = 'Bank Withdrawal' then current rows 'Closing Balance' = previous rows 'Closing Balance' + current rows 'Begin Balance'
3. (**) When a new row is created, then previous rows 'Closing Balance' is carried forward to the new row
4. (**) Check if row column 'Expense Paid By' value exists in Table2 column Division.
If Not exists, then carry forward the previous rows 'Closing Balance' to the current row
If Yes, and if Status = 'Paid", then include the current rows column 'Expense Amount' value in the 'Closing Balance' calculation
Could you please advise on the solution.
Many thanks and look forward to hearing from you.
Regards,
Don
I have a defined a Cash Book table and a setup table which handles petty cash of various departments.
I need help in constructing a formula for deriving the closing balance. This is based on some criteria as described below. Also, attached a sample workbook with the description for your reference.
Case:
There are various divisions which contributes to the daily organizational expenses.
Expenses which are paid only by Division 'Third Division', needs to be paid back to them.
There is a Setup table Table2. This has the division names where the amount needs to be paid back to that Division. All those Divisions would be listed here. This will be reflected in Table1 column Closing Balance.
Requirement in Table1:
The column 'Closing Balance' should be calculated as follows:
1. Closing Balance= Previous rows 'Closing Balance' + current row's 'Begin Balance' - current rows 'Expense Amount' (**). Any new 'Begin balance' will be added to the 'Closing Balance'
2. (**) If column 'Type' = 'Bank Withdrawal' then current rows 'Closing Balance' = previous rows 'Closing Balance' + current rows 'Begin Balance'
3. (**) When a new row is created, then previous rows 'Closing Balance' is carried forward to the new row
4. (**) Check if row column 'Expense Paid By' value exists in Table2 column Division.
If Not exists, then carry forward the previous rows 'Closing Balance' to the current row
If Yes, and if Status = 'Paid", then include the current rows column 'Expense Amount' value in the 'Closing Balance' calculation
Could you please advise on the solution.
Many thanks and look forward to hearing from you.
Regards,
Don