Creating DP and WD Files

We may create more files if we have lots of banks to avoid delay in our google sheets when many members uses it at one time.

Step 1. Open the current file that we have and make a copy.

Step 2.   Rename the file. Please follow the example file name:

VND-BJ88 FEBRUARY 2025 DEPOSIT 001

VND-BJ88 FEBRUARY 2025 DEPOSIT 002

VND-BJ88 FEBRUARY 2025 WITHDRAWAL Step 3. There are formulas we do not need to change anymore like in column L, hide the column and, continue to delete old records.

step 3. after deleting the details, Update the formula in column A

just follow the same formula in the sheet and, just update the date, day/mm/yy

Check the formula under current balance it must be =IF(A$INPUT NUMBER="Approved",L4,0) in this example approved remarks is in A45 so the formula must be =IF(A$45="Approved",L4,0) because, if you go back to column a "approved" is under on cell A45

below this formula we must put =IF(A$45="Approved",F4+D5-E5,F4) still the approved cell is in teh formula

 > copy this formula until the end of the sheet

-the formula for the running balance is the last cell on the sheet on the current balance -the opening balance can be manually encoded (this is the ending balance of last month)

do some testing to check whether the formula is working, always put a date to make it work

Do this in all active banks

you may remove the banks that we no longer used and make sure to also removed it in our DS.

Step 4. Update formula in DS

The formula for the running balance ='Sheet Name'!C1

the first one is the running balance of vtb w2 and, total is total of each transaction for the whole month

all the amout in the left side table is the total amount on the right, just put the cell number to automatically display the total amount for the whole month

formula for opening balance ='Sheet Name'!C2

the total will be calculated as sum of all the amount of transaction on the left side of the table including the opening balance

change the date dd/yy

please take note of the formula for each transactions; put the formula on the first day of the month; and just drag down to apply until the end of the month

take note of the formula

deposit - just change the sheet name for done DP remarks =sumproduct(('VTB - W2 - CTCUTTVTTAM'!D$4:D$22999)*('VTB - W2 - CTCUTTVTTAM'!B$4:B$22999='VTB - W2 - CTCUTTVTTAM'!A4)*('VTB - W2 - CTCUTTVTTAM'!J$4:J$22999='VTB - W2 - CTCUTTVTTAM'!A$32)) for done WD remarks - just change the sheet name =sumproduct(('VTB - W2 - CTCUTTVTTAM'!E$4:E$22999)*('VTB - W2 - CTCUTTVTTAM'!B$4:B$22999='VTB - W2 - CTCUTTVTTAM'!A4)*('VTB - W2 - CTCUTTVTTAM'!J$4:J$22999='VTB - W2 - CTCUTTVTTAM'!A$32))*-1 for 3rd party (for refund) =sumproduct(('VTB - W2 - CTCUTTVTTAM'!D$4:D$22999)*('VTB - W2 - CTCUTTVTTAM'!B$4:B$22999='VTB - W2 - CTCUTTVTTAM'!A4)*('VTB - W2 - CTCUTTVTTAM'!J$4:J$22999='VTB - W2 - CTCUTTVTTAM'!A$34)) for 3rd party (refunded) =sumproduct(('VTB - W2 - CTCUTTVTTAM'!E$4:E$22999)*('VTB - W2 - CTCUTTVTTAM'!B$4:B$22999='VTB - W2 - CTCUTTVTTAM'!A4)*('VTB - W2 - CTCUTTVTTAM'!J$4:J$22999='VTB - W2 - CTCUTTVTTAM'!A$35))*-1 test/other =sumproduct(('VTB - W2 - CTCUTTVTTAM'!D$4:D$22999)*('VTB - W2 - CTCUTTVTTAM'!B$4:B$22999='VTB - W2 - CTCUTTVTTAM'!A4)*('VTB - W2 - CTCUTTVTTAM'!J$4:J$22999='VTB - W2 - CTCUTTVTTAM'!A$44)-('VTB - W2 - CTCUTTVTTAM'!E$4:E$22999)*('VTB - W2 - CTCUTTVTTAM'!B$4:B$22999='VTB - W2 - CTCUTTVTTAM'!A4)*('VTB - W2 - CTCUTTVTTAM'!J$4:J$22999='VTB - W2 - CTCUTTVTTAM'!A$44)) top up =sumproduct(('VTB - W2 - CTCUTTVTTAM'!D$4:D$22999)*('VTB - W2 - CTCUTTVTTAM'!B$4:B$22999='VTB - W2 - CTCUTTVTTAM'!A4)*('VTB - W2 - CTCUTTVTTAM'!J$4:J$22999='VTB - W2 - CTCUTTVTTAM'!A$42)) unclaimed =sumproduct(('VTB - W2 - CTCUTTVTTAM'!D$4:D$22999)*('VTB - W2 - CTCUTTVTTAM'!B$4:B$22999='VTB - W2 - CTCUTTVTTAM'!A4)*('VTB - W2 - CTCUTTVTTAM'!J$4:J$22999='VTB - W2 - CTCUTTVTTAM'!A$33)) Bank interest/fee =sumproduct(('VTB - W2 - CTCUTTVTTAM'!E$4:E$22999)*('VTB - W2 - CTCUTTVTTAM'!B$4:B$22999='VTB - W2 - CTCUTTVTTAM'!A4)*('VTB - W2 - CTCUTTVTTAM'!J$4:J$22999='VTB - W2 - CTCUTTVTTAM'!A$37))*-1 Bank Rebate =sumproduct(('VTB - W2 - CTCUTTVTTAM'!D$4:D$22999)*('VTB - W2 - CTCUTTVTTAM'!B$4:B$22999='VTB - W2 - CTCUTTVTTAM'!A4)*('VTB - W2 - CTCUTTVTTAM'!J$4:J$22999='VTB - W2 - CTCUTTVTTAM'!A$36)) Bank Fund Received =sumproduct(('VTB - W2 - CTCUTTVTTAM'!D$4:D$12999)*('VTB - W2 - CTCUTTVTTAM'!B$4:B$12999='VTB - W2 - CTCUTTVTTAM'!A4)*('VTB - W2 - CTCUTTVTTAM'!J$4:J$12999='VTB - W2 - CTCUTTVTTAM'!A$43)) Bank Fund Transfer =sumproduct(('VTB - W2 - CTCUTTVTTAM'!E$4:E$33999)*('VTB - W2 - CTCUTTVTTAM'!B$4:B$33999='VTB - W2 - CTCUTTVTTAM'!A4)*('VTB - W2 - CTCUTTVTTAM'!J$4:J$33999='VTB - W2 - CTCUTTVTTAM'!A$40))*-1 Marketing Spending =sumproduct(('VTB - W2 - CTCUTTVTTAM'!E$4:E$12999)*('VTB - W2 - CTCUTTVTTAM'!B$4:B$12999='VTB - W2 - CTCUTTVTTAM'!A4)*('VTB - W2 - CTCUTTVTTAM'!J$4:J$12999='VTB - W2 - CTCUTTVTTAM'!A$38))*-1 Sales Commission ( for WD)

=sumproduct(('VTB - W2 - CTCUTTVTTAM'!E$4:E$12999)*('VTB - W2 - CTCUTTVTTAM'!B$4:B$12999='VTB - W2 - CTCUTTVTTAM'!A4)*('VTB - W2 - CTCUTTVTTAM'!J$4:J$12999='VTB - W2 - CTCUTTVTTAM'!A$39))*-1 Settlement (for DP)

=sumproduct(('VCB - D4 - CTTNTTSLX '!E$4:E$12996)*('VCB - D4 - CTTNTTSLX '!B$4:B$12996='VCB - D4 - CTTNTTSLX '!A4)*('VCB - D4 - CTTNTTSLX '!J$4:J$12996='VCB - D4 - CTTNTTSLX '!A$41))*-1

the total is calculated by the total daily amount of each transaction including the opening balance. do not drag down the formula here.

go to the second row and add the total daily transaction for the second day of the month plus the ending balance for yesterday =U3+F4+J4+K4+L4+N4+O4+P4+Q4+R4+S4+H4+M4 at this point you may start dragging down the formula until the end of the month. (sales commission; number of wd and dp transaction are not included in total. please note)

Make sure to test all remarks.

Step 5. Bank Balance

for txn count & fund in amount you can only count the banks in that sheet, in this example we can only count the txn for MSB - D1 - CTOMA0729

VCB - D4 - CTTNTTSLX Let's have VCB - D4 - CTTNTTSLX for example, for txn count =countifs('SheetName'!C5:C20001,"*",'SheetName'!B5:B20001,D1) =countifs('VCB - D4 - CTTNTTSLX '!C5:C20001,"*",'VCB - D4 - CTTNTTSLX '!B5:B20001,D1) for fund in amount =sumproduct(('SheetName'!D4:D19997)*('SheetName'!B4:B19997=D1)) =sumproduct(('VCB - D4 - CTTNTTSLX '!D4:D19997)*('VCB - D4 - CTTNTTSLX '!B4:B19997=D1)) for bank balance =IMPORTRANGE("insert url of the bank", "SheetName!C1") =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1288C0VJwBoo9XPzrNufERo7C-qT88Bcv8CQcLqcLE9o/edit?gid=1861045095#gid=1861045095", "VCB - D4 - CTTNTTSLX!C1") OR ='SheetName'!C1 ='VCB - D4 - CTTNTTSLX'!C1 Target txn/remarks/requested top up are manually encoded. Do this in all sheets then to pull up a value from one file to another in the bank balance you may use this formula just replace the sheet name to bank balance and the cell of the value you want to display. =IMPORTRANGE("insert url of the bank", "SheetName!C1") =IMPORTRANGE("https://docs.google.com/spreadsheets/d/18fhodiSCJJRHc2ffado1LoQO6JH5l5GMKXVGfkjWEEE/edit?gid=1575232983#gid=1575232983", "BANK BALANCE!E7")