• 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.

Dynamic Charts w/ OFFSET & Named Range

J

Jeff C

New Member
Hi, I can't seem to get my dynamic chart to work. I have a pivot table that displays savings rates by month for each individual store. I want to create a summary chart that shows the average of all stores for each month. However, when I follow the advice on this page, the chart will not update when I click on the 'Month' slicer.

I attached a simplified file that shows what I did. I followed the advice from this Chandoo page:
http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/.

COUNTA doesn't work, because there is a formula in the entire range... so I use COUNT instead and make a Named Range called 'Trend' with the following formula:
=OFFSET(Sheet1!$F$2,0,0,1,COUNT(Sheet1!$F$2:$CE$2))

I then insert a line chart using the Named Range 'Trend'. The chart displays the correct information, but then will not update when I click on the 'Month' slicer.

Thanks for any ideas about how to fix this!
Jeff
 

Attachments

Two corrections. In the formulas, the first two were looking at the wrong column. The other things was just how the Named Range was passed to the chart. Rather than stating it in the general "chart data" range, you need to give it to the Series Value range. Then the chart axis will update as you slice different things.
 

Attachments

Awesome! It works... thanks so much!! One quick follow-up question... does the Series Value range always include the file name? If I change the file name, will I need to change the Series Value range accordingly?

Thanks again,
Jeff
 
Yes, the name will always be included. This is because it's possible to have multiple files open that have the same name in each, but each workbook only has a unique name. You do not need to update in this case, since the workbook being referred to is the same as where formula is, so any change to filename will get captured. This is similar to how if you have a formula referring to a cell, and that referenced cell moves, you are okay.

With only the one workbook open, you can use just the sheet name though, and XL will do the hard work. In your chart, I just had to write:
=Sheet1!trend
and click 'ok', and XL filled in the file name, rather than me having to type it all out.
 
You must log in or register to reply here.
Back
Top