Hello,
I am given a 24 month rolling "data" report each week, along with two other monthly reports ("Production" & "Inventory", which i need to use for creating a customer report.
The example customer report is the "CPB" tab
Currently, I am using independent formulas, in each cell, to perform these calculations (mostly sumifs and index/match criteria)
As the report size increase, the slower the file works (sometimes not working at all - getting "stuck")
I believe it may be easier to break down my requests into separate phases (based on most critical to least needs), so I would like to try and start with columns E through I only
of course, if someone has a better ideas on how to make the entire report better with a macro, I would be thrilled :).
Some Notes to help:
Column B of the CPB tab - the Location names are fine as a manual entry because it doesn't seem to change too often - it may need an occasional name added, but rarely one ever removed
Cell B5 is a MMM-YY reference (data validation list), which allows the user to toggle back and forth to view results based on the selected month-year.CPB VBA Example.xlsm
***** Columns E-I, of the CPB tab (main need for assistance - phase I)
| Columns E - I, of the CPB tab, are the columns I need to try and make more efficient, using a macro instead of the "sumifs" formulas in each individual cell |
| Columns E - I of the "CPB" tab, are sumifs based from the information from the "Data" tab |
| Columns E - I of the "CPB" tab are currently calculated as follows: |
I have attached a two month report example (much smaller than actual 2yr report)
Any help/suggestions to improve the efficiency of this report would be very very greatly appreciated!
Kind Regards,
Paula
| Sum of the Sales, from Data tab, column G |
| IF, column I, of the Data Tab (Well Name), matches the well name in cell B#, of the CPB tab |
| And IF, column M, of the Data Tab (Product Application), matches the headers of cells E5, F5, G5, H5 & I5, of the CPB tab |
| And If, column N, of the Data tab (Invoice Mo-Yr), matches the Month/Year selected in cell B5 |