Latest&last update - just format tweak + 2nd chart for period selection.
Excel file: http://bit.ly/8HwVSm
More choice = more fun!
I've just installed xl2010(beta) on my desktop and in the first place corrected one bug which showed up only in new excel. I added some transparency (sorry xl2003 users but probably your visual experience with my file will be slightly lower now - but it's only a question of tweaking 3 chart series color - average lines and grey area). I am not a fun of transparency but in this case it enabled to see gridlines under area chart and thick average lines.
Summering up the functionality implemented so far:
New excel format can be picked in download section (version 6 xlsm) - but I am not sure about compatibility with xl2007 (in xl2010 it works).
I came to the limit of sumproduct formula usability (speed of my chart dropped with the previous version and I didn’t feel well with those lags). Therefore I decided to omit my first assumption and I added pivot table so that I can use much more efficient GETPIVOTDATA formula.
Macro wouldn't be needed if I didn't put 3 filter combo with "All" option available with previous version. As GETPIVOTDATA can't (as far as I tested it) deal with them properly I put some code which hides a pivot field which has 'All' attribute selected by user.
Excel file: http://bit.ly/6d0gQu
One pivot in background + 35 lines code (very basic one) and we have speeeeed.....
No new features but no more lags (not tested in xl2007).
I added a new version:
Those 3 more combo are big step forward in functionality - but step back in performance. Sheet should be still OK to work with - but it is probably max usage of sumproduct formula
Screen shot: http://bit.ly/7QaQrm
Excel file: http://bit.ly/7w5uOq
I created this site to present my version of dynamic chart for 'Visualization Challenge #2' contest launched on Chandoo blog.
As I didn't have enough fresh ideas to make whole dashboard I focused on one chart only. My basic inspiration was a chart with one series highlighted I recently saw on one of the popular Blog (probably PTS Blog but I am still looking for it). Dynamic features applied:
The chart doesn't use any runtime VBA code. I used only one code in design time - to unpivot presented data (to put them into vertical layout which is more flexible and natural to use for me). I attached the code in separate file in case you would find it useful http://bit.ly/7SSiXK. Just remember to have all text fields on the left side of first value field. I re-randomized data - to have some categories crossed.
As I didn’t use VBA I found putting pivot table to be to frustrating (formatting!!!). Therefore I exclusively used excel built-in formulas.
I haven’t tested my chart performance on xl2007 yet. On xl2003 chart refresh speed for filtering in ComboBoxes should be OK, for scrolling: moving scroll point or clicking scrolls is acceptable but clicking arrows sometimes jams for several seconds.
Thanks for your comments.