Sample data: sample.xlsx
So here is what I am hoping to do.. I am looking to create a bar chart that will show the average number of "Total Leads" per "Type" to date. I want to make sure to filter out future dates so that the average is not thrown off. Normally, I would set up a filter and select the dates I need to run, but -- 1) this same thing needs to be done for last year, so after I can build this table, I will do the same for the 2014 file and create a consolidated table from which I will create a bar chart; 2) those who will be taking on this report are less excel savvy than I am and if I can create some thing that does most of the work for them that would be great!
In essence when the team wants to run the report, they would refresh the pivot tables to update the "dynamic" date, then the consolidated table (which would just link, to one place, the data I need from each individual table) would update and so would the bar chart.
I think this might work with the slicer, since I believe you can add macros to slicers. I have no idea if this is the case, or (if it is) how to do it.
I tried to add a label filter using "less than: Today()" but the Today() did not work in the field.
Let me know if you need more information, or if this is not possible. Any help would be greatly appreciated!
p.s. given the level of depth this small (and not too technological team) has been asked to review their "events" based on leads per type, per week, per year, against forecast and against last year.. I told them that they need a full system and not excel. so if anyone can recommend a better tool to easily create reports, that would be great too!