Error in Chart using named range 1

[cctanaka]

Hi,

I am trying to create a Dynamic Chart using named ranges.

1. I created two named ranges as follow:

CatX - workbook scope, with the formula: =OFFSET(INDIRECT("'Week Data'!$B$" & Metas!$C$4+2);0;0;Metas!$C$5-Metas!$C$4;1)
CatY - workbook scope, with the formula: =OFFSET(INDIRECT("'WeekData'!$G$" & Metas!$C$4+2);0;0;Metas!$C$5-Metas!$C$4;1)

There are sheets called 'Week Data' and 'Metas'

When I use CatX or CatY or both in the SERIE of a column chart, like this:

=SERIE($G$2;CatX;CatY;1)

it shows an error message.

I have tested parts of the Offset formula and they worked. Like those:

=INDIRECT("'Week Data'!$B$" & Metas!$C$4+2)

=sum( OFFSET(INDIRECT("'WeekData'!$G$" & Metas!$C$4+2);0;0;Metas!$C$5-Metas!$C$4;1) )



Where is the error?

Thanks.

Carlos César tanaka
Curso de Excel / Access
MPR Informática

 

[vgulielmus]

I remember that in series() must use absolute address.

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[SkipVought]

hi,

Is it [highlight #FCAF3E]Week Data[/highlight] or [highlight #FCE94F]WeekData[/highlight]??? It can't be both and that alone would be a show stopper!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

I think that there's a much simpler way of using the OFFSET() function to create a dynamic range.

You already know that the OFFSET function has 5 arguments. You are trying to vary the first argument. If it's referencing various ranges in the same table, then the first argument (I call it the ANCHOR reference) should be static. Simply vary the second argument, the row offset from the ANCHOR reference; one numeric value!

Second, the Range Names should be assigned to the x & y series in Select Data (when you right-click the chart) and assign each Name when you EDIT the AXIS and assign the name AFTER THE ! following the SHEET NAME like...

[tt]
=YourWkbkName![highlight #FCAF3E]CatX[/highlight]
[/tt]

So you simply change the row offset value and the chart changes.

The formula I used in the Name Manager is...
[tt]
=OFFSET(Sheet4!$A$1,RowOff,0,2,1)
[/tt]
where RowOff is the Named Range that contains the offset value that changes. This value can be varied using a control.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

Where is the error?

As Skip pointed, it's because of missing name's parent in chart formula, it's different from a formula in a cell. For workbook level you need =SERIE($G$2;YourWkbkName!CatX;YourWkbkName!CatY;1), for sheet level names =SERIE($G$2;YourWksName!CatX;YourWksName!CatY;1)



combo

 

[cctanaka]

The solution presented by SkipVought has worked, besides it is simpler.

Thanks.

Carlos César tanaka
Curso de Excel / Access
MPR Informática