Dynamic chart range becoming hard-coded # tables

[andydtaylor]

Hi,

I have a dashboard I have compiled which uses a table output from PowerQuery. There's a VBA script doing a few formatting things too.

I would like the chart series to scale to reflect size of table returned. I'm a bit confused because this seems to work for a while and then the range becomes hardcoded and doesn't scale. I think the dynamic connection gets lost on a chart which can occasionally have no data to plot.

So I have 2 questions:

1. Is there specific table-chart linking functionality which I can check/perhaps reset at runtime in vba?
2. Or going back to the way I would have approached this in the past - is there a way to have Excel intelligently update the source range without losing chart formatting?

Thanks,

Andy

 

[oldbrewer]

keeping it simple - yes excel will plot A1 to A3 if only three data points, add one more in A4 and the chart will plot 4 points

note this is not 3 points and say 7 blanks it is 3 points only

is yjis what you want to do ?

 

[andydtaylor]

Hi,

Yes this is the result I want. However, for reasons not-yet proved out the connection with the table seems to break sometimes for one or more charts.

Keeping to your example lets say that data now occupies A4, well my scenario is that 2 charts update to include A1:A4 and one does not (it keeps A1:A3) for reasons not yet confirmed. If I try to adjust for this behavior by forcing the chart source range myself then I lose all the formatting because that part of Excel functionality is nuts....

 

[oldbrewer]

on the third chart it must be a problem with how the named range and or data location is defined - write down all the series deetails for cchart 1 and compare with chart 3

if still stuck, post above on here.....

 

[andydtaylor]

Hi,

I am still stuck.

My charts are still losing their link to the table.

I am presently on my output sheet with "Internet and Web Services" selected. My Power Query output table has the range "Output!$B$5:$C$18". This is how I had saved it when I last worked on the document.

Chart 1 range =Output!$B$5:$C$18
Chart 2 range =Output!$B$5:$C$18
Chart 3 range =Output!$B$5:$C$18

I now change category to "Travel and Reservation Services" and hit refresh. My Power Query output table now has the range "Output!$B$5:$D$18"

Chart 1 range =Output!$B$5:$D$18
Chart 2 range =Output!$B$5:$D$18
Chart 3 range =Output!$B$5:$C$18

I now change category to "Consulting Services" and hit refresh. My Power Query output table now has the range "Output!$B$5:$H$18"

Chart 1 range =Output!$B$5:$H$18
Chart 2 range =Output!$B$5:$H$18
Chart 3 range =Output!$B$5:$C$18


I was at a colleagues desk earlier and he had a scenario where charts 2 and 3 had lost their link to the table. What I find interesting is that the chart source range doesn't keep the dynamic name, instead it appears hard-coded. If I recreate chart 3 by cloning charts 1 or 2 and selecting/deselecting the appropriate data series it will become dynamic again.

What can I do to correct this behavior?


Thanks,

Andy

 

[oldbrewer]

chart 3 goes to $c$18 in all 3 cases - is that correct ?

 

[andydtaylor]

Correct, yes.

To clarify my earlier point on not keeping the dynamic name what I meant is that I find it surprising the charts don't display "Toutput", the table name as their source. Is this by design or symptomatic of a problem? If I edit the charts to specify this or an alternative manually created dynamic range which mirrors the table range the verbose name disappears and the "normal" range address takes its place.

 

[oldbrewer]

I do not think I can help you - to clarify the problem, say in A1 to A10 is your data, and you name the range A1 to A10 as mydata, you create a chart using mydata for the source data, the chart is fine and data range is defined as "mydata" - what happens to cause the data range to become A1:A10 ???

 

[andydtaylor]

I think that I must have happened across a table/power query bug.

What I have done and what seems to be working is to create my own dynamic ranges that pick out the data ranges for specific charts and link the charts that way. Which is the way you used to have to do it before tables.....