Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Error in Chart using named range 1

Status
Not open for further replies.

cctanaka

IS-IT--Management
Aug 24, 2005
19
BR
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
 
I remember that in series() must use absolute address.

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
cctanaka said:
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
 
The solution presented by SkipVought has worked, besides it is simpler.

Thanks.

Carlos César tanaka
Curso de Excel / Access
MPR Informática
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top