Basically I need a macro to populate raw data (‘Detail’ sheet) which will be refreshed from an SQL server and present them in a specific sorted table. As the user interface must be interactive, I will need you to create them with a Slicer Selection tools which is part of pivot table (Excel 2010 an later).
It looks like simple, but it just looks like simple for the user, I think it would need an expert and an experienced programmer in Excel VBA and macro.
The output (‘Report’ sheet) will be like a pivot table but in my design where Column A (Region) and B (Cluster) on the left will be populated from column C and B of ‘Detail’ Sheet.
Column D (Forecast date) onward will have headers of the date populated from column D (‘Detail’ Sheet) and each row in column D onward will be filled by the count of data in column A (‘Detail Sheet) each row on the report table will correspond to column A and B on their left. The number of columns (date headers) on column D onward will be automatically determined by the macro after it populates ‘Detail’ Sheet based on Slicers selection. Column D onward must have subtotal count in each column, and 1 cell of Total below them.
Column C (Total each row) will be presented like: 10(4) meaning, 10 is total number of tasks, Sum of data from Column D onward and (4) is number of completed tasks where indicated by column E (Actual date) in ‘Detail’ Sheet.
When user clicks on cell in column B (cluster) in the table report, the macro should create a new sheet/tab which should be named with the selected cluster/text and shall contain all data extracted from ‘Detail sheet’ with regard to the selected cluster only.
It will need 3 slicers selection: user can select Region, Month, and choose to show All data or just Outstanding (the number of tasks which not completed yet), in other words Outstanding is where the forecast date doesn’t have an actual date in ‘Detail’ Sheet.
For Month Slicers selection, you will need more trick to apply it on the report table because the raw data on ‘Detail’ sheet doesn’t provide you with Month data and you are not allowed to change/add column in this sheet unless it is an automatic data update, instead you can get them from the forecast date to pull out their each month name.
For instance, when the user selects January, February, March, and December, the report table should only show the selected month data. Then the user select Outstanding which means the table should only show the number of outstanding tasks in selected month(s) and region(s).
It’s the same logic when you use data filter for a listing, but more interactive. The raw data connected and refreshed by an SQL server connection therefore the range cannot be static; the macro should read for how many rows in the ‘Detail’ Sheet and specify the range for the pivot table.