Nick G

New Member
Joined
Feb 11, 2019
Messages
7
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top