So I want to apply a simple formula to calculate closing Balance i.e.
'Closing Balance= Opening balance + purchase - sale'
But there are many complexities.
the main issue is. I have 3 workbooks, Workbook 'O' and Workbook 'T' and Workbook 'CT'
Workbook 'O'- containing Opening balance as on 1st feb for over 250 different product
Workbook 'T'- containing daily transactions of purchase & sale of those product. Those transactions vary from 200 to 400 per day.
Workbook 'CT':- It is nothing but mere copy paste of workbook 'T'. I consolidate all transaction done on given day in workbook 'T' in this workbook so I get overall transaction done in that month
So, what I want is that as I consolidate all transaction of Workbook 'T' that I receive daily in workbook 'CT' on daily basis, workbook 'O' containing opening balances should,
1- match a products in the transactions in workbook CT and then add all purchases and subtract all sale for that day and give me the closing balance for that day for that product
2- do the above thing for all the products
3-If a new product is bought then add the name of that product in Workbook O and then take opening balance as zero and add all purchase and subtract all sales and give me the closing Balance for that day for that newly added product.
I just don't know what to do. Any help would be appreciated. Any complex formulas, Function,VBA macro anything will do.
Please help,
Thanks in advance
'Closing Balance= Opening balance + purchase - sale'
But there are many complexities.
the main issue is. I have 3 workbooks, Workbook 'O' and Workbook 'T' and Workbook 'CT'
Workbook 'O'- containing Opening balance as on 1st feb for over 250 different product
Workbook 'T'- containing daily transactions of purchase & sale of those product. Those transactions vary from 200 to 400 per day.
Workbook 'CT':- It is nothing but mere copy paste of workbook 'T'. I consolidate all transaction done on given day in workbook 'T' in this workbook so I get overall transaction done in that month
So, what I want is that as I consolidate all transaction of Workbook 'T' that I receive daily in workbook 'CT' on daily basis, workbook 'O' containing opening balances should,
1- match a products in the transactions in workbook CT and then add all purchases and subtract all sale for that day and give me the closing balance for that day for that product
2- do the above thing for all the products
3-If a new product is bought then add the name of that product in Workbook O and then take opening balance as zero and add all purchase and subtract all sales and give me the closing Balance for that day for that newly added product.
I just don't know what to do. Any help would be appreciated. Any complex formulas, Function,VBA macro anything will do.
Please help,
Thanks in advance
