Excel 2007 dynamic chart range problem

[Reinier10]

Hi, I need some help with the following problem:

I have some tables in Excel that are updated monthly. The data is plotted in charts, which I know can be made dynamic to capture the new data every month.
To create the dynamic chart I've created a dynamic range using an offset formula,, including a count procedure, for example:

OFFSET('Return MD'!$B$2;1;1;1;COUNTA('Return MD'!E8:AA8))

The problem is that our new Excel 2007 version doesn't accept the dynamic ranges in the chart. The range itself works perfectly , which I checked with
INDEX(named_range;1;12)

In the previous Excel version this worked, fine!

Hope someone can help me with this one, seems to work for everyone else.

 

[SkipVought]

Hi,

I have never used OFFSET to define the ENTIRE data range.

I have used OFFSET to define EACH row or column of data, corresponding to each series.

So if my data were on sheet ChartData, and on of my range names were Load, then in the chart, Source Data, Series Tab, in the Values for the Load series, my reference would be...
[tt]
ChartData!Load
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Reinier10]

Hi Skipvought, thanx for your reply. Of course I meant to say I defined the range to refer to the series, so that was not the problem.
What I see now is that you use the sheetname in the reference to the named range. I totally forgot that!
Adding the sheetname solved the problem, so thanx for the example.

 

[SkipVought]

Reinier10,

I initially misread your formula, which, of course, refers to a single row of data. I should have deleted my first 2 comments.

Glad its working for you now.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}