Dynamic Chart Data Table

I know how to build Dynamic Chart but is there a way to create dynamic "Chart Data Range"?
If you build a table using OFFSET formula
Let say
=OFFSET('Chart RFT'!$A$3,,,'Chart RFT'!$A$2+1,'Chart RFT'!$G$3+1)
where
'Chart RFT'!$A$2+1 is the height
and
'Chart RFT'!$G$3+1 is the width

and call it ChartTable

you can use it in the "Chart Data Range" but as soon as you close the "Data source" box, the Named Range will change to the table reference
='Chart RFT'!$A$3:$B$6
and any update to the hight or width is not longer reflected on the ranges.

Any ideas?

Unfortunately not. As you have discovered whilst the select data dialog will allow you to use a named range as the source it converts that to normal range references for each series.

If you could it would allow for dynamic series as well as dynamic categories.

Thank you Andy
So is the a way to build Dynamic stack column Chart?

I need the Series to appear only when they are needed.
I have dates on top of the table,(5 cells) categories (basedon formulas)on the left an the table is based on formulas.

Let say today I have just one entry for "cat 1"
This will show first series only.

Tomorrow I will have 2 for "cat1" and 3 for "cat2".
So what I would like to see is on chart is two days with :
first day with "cat1" and
the second day with "cat1" & ""cat2".

Is this do-able?

Sorry that's a little confusing. when you say cat do you mean series?

Can you post a workbook example with the data and chart for each day, perhaps that would make it clearer.

Hi Andy
Entries are made in "Jan-Mar 2014" tab.
With this set up I would like to see the "ef" category on 17/02/2014

But this "ef" may not be needed till 17 and let say tomorrow a new category will come up in cell A6 gfh) and that should be visible only tomorrow
Excelf2.xlsx

With named ranges you can extend the number of data points in know series.
You can't (that I know of) add new series.

To add new series you would need to use VBA code.

Alternatively you could change your data layout and use pivot table/chart

Thank you Andy for the alternatives -still PT need to be manually refreshed.

With the VBA code, would you mind to give an example?
Would that be based on Intersect in column A for each series?
?

Please Login or Register  to view this content.

I have updated your formula in column G, which calculates width of category, to handle any potential errors.

You will still need to run the macro when new data changes.

Thank you Andy.


Another feature that MS can incorporate in next Excel version.