How to use dynamic named range in a chart

[espenskeie]

Hi

I have created a bunch of named ranges, and now I want to use them in a chart.

The idea is to show the last four weeks of data... But I cannot get it to work. I just get an error Message saying that my formula has an error when I type "=RollingFourWeek_AustraliaUSD" in the "Select Data" section in my chart...

My named range is quite Heavy, but it show the correct range when I check it. Basically I am checking if there are less than four weeks of data, if so I want to just Select what there is.. If there are more than four weeks, then Select the last four:

=OFFSET(Cash!$E$3;2;IF(MAX(COLUMN(Cash!$E:$AZ)*(Cash!$E$3:$AZ$3<>""))<=8;0;MAX(COLUMN(Cash!$E:$AZ)*(Cash!$E$3:$AZ$3<>""))-5);1;IF(MAX(COLUMN(Cash!$E:$AZ)*(Cash!$E$3:$AZ$3<>""))<=8;MAX(COLUMN(Cash!$E:$AZ)*(Cash!$E$3:$AZ$3<>""))-4;-4))

Kind regards
Espen

 

[Derek Brown]

Look at the "Dynamic Charts" section in the following link to see if that can help you.
Jon's Excel Charts and Tutorials - Index

 

[Jon Peltier]

Here's a tutorial on my blog that may help:

Dynamic Chart Source Data.

The most likely cause of your problem is that you have not referenced the Name with its parent sheet name. Enter this in the Select Data dialog:

"=Cash!RollingFourWeek_AustraliaUSD"

Also note that Excel will replace this Name by the address of the Name in this dialog, so it won't be dynamic. That whole tutorial cited above contains a number of ways to leverage VBA to keep the chart updated when the Name changes size.

The best way to handle this, if at all possible, is to use a Table for the data. But I suspect your Name is more complicated than that.