• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Charting with slicers - that dynamically change the size of the chart

J

jlies12

New Member
Hello all,


Wanted to start off by saying that this is a great community and I'm glad I've stumbled upon such a valuable resource and only wish I could give back 1% of what I've learned.


To the fun stuff:


I have a pivot chart (bar style) that has a list of employees and the hours that they worked (billable, non-billable, pto, etc). I also have slicers that limit the data to specific department heads, managers, types of employees, and/or if the employee met his/her billable goal.


With 300 employees, the chart has to be quite long (with no limits on the data being presented). However, when we have few results based on the items selected (say: those that report to manager 1 and met their goal = 10 employees, the chart stays the same height and the bars are huge.


Is there any way to have the chart resize based on the number of results. My thought process was to do a counta on the pivot table and use that as a percentage of 300, then take that and multiply it with the original height of my chart with all 300 employees and then macro that to change the height of the chart. So far, no luck.


Any help is greatly appreciated! I also have a simplified file that I could hopefully share some how to help understand my dilemma.
 
You have to create a dynamic named range for your X-Values.

Here is the solution. Create a named range with the following formula:


=OFFSET('data - current'!$I$3,,,COUNTA('data - current'!$I:$I)-1,1)


Then Go to your source data and for the xvalues source. Enter this.(fill accordingly)


='workbookname.xls'!namedrange
 
Thanks Montrey; I appreciate your help.


I'm not sure I'm where I need to be still. Whenever I use the slicer, the size of the bars still change and the height of the chart does not. I assume this might work on a chart that isn't tied to a pivot table?


Or, which is quite likely, I didn't completely utilize your solution. Any further help would be asking too much, but appreciated immensely.
 
The named range formula I posted Counts the number of employees from the employee column on your data tab then makes a range the size of how many were counted. So if new data were to be entered the range will adjust automatically.


I am not quite sure how or what you do with ur data. Or what the slicer is doing when it is used. Maybe you can explain what happens when you use the slicer. I dont have excel 2010 so... :(
 
Thanks for the follow-up. Yeah, the slicers are dynamically change the population in the chart but not the chart height. Basically the slicers act as a filter for various attributes of the population (i.e. only display employees if they report to x manager and are in department y)


So if I size my chart to include all 300 employees, then filter to just x manager and department y, the result is only 10 people are displayed in the chart. But the catch is that the height of the chart never changes and the bars for those 10 employees become huger and the chart is really unusable. I need to be able to write some VBA code to say count the number of items in the pivot table (since the slicers interact with the table itself) and apple that proportion to size of the chart when all 300 employees are present).


I wish I could explain it a bit better, but I'm guessing that the pivot charting (especially with slicers) is a bit newer and people aren't using them quite like I am here.


Thanks again for everything though.
 
You must log in or register to reply here.
Back
Top