Graph Data Range not fixed

[Pitbull]

Hello everyone!

I am trying to create a macro that will change my graph's
data range in function of a specific cell's data.

So, for example, if my cell a1 = Month3, I want my graph
data range to be from a5 to d5.

but, if my cell a1= month5, I want the graph data range to
be from a5 to F5.

I tried recording the macro and selecting my data range
using this when I go to the graph/source data/data range --
-> =indirect(a1)

But then, it just takes this indirect a1 and makes it into
the given range (for example month3 turns out to make my
data range---> =a5:d5

Hope this is not too confusing.

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("OEE Raw
Data").Range("A5:F6"), _
PlotBy:=xlRows

The 3rd line is what I need to change I think...
source:=indirect type of function thing...

Can anyone help?

Thanks

Pitbull

 

[Don Guillett]

insert>name>define>enter a name such as rowrange>in the refers to
=offset($a$5,0,0,1,$a$1)
in series
=yourworksheetname!rowrange

now try if 3 in a1 or 5 in a1

 

[Guest]

You can also try using named ranges using the offset with count like this....

=OFFSET('FlrSpc Data'!$A$2,0,0,COUNTA('FlrSpc Data'!$A:$A) + 1,3)

You then can put the named range in the source data range field of the
graph. So as data is added, the graph grows accordingly.

HTH, Dean.

 

[Pitbull]

Don, are you sure this will help me with the graph data
range though... played with it and still at same point.

Tx
Pittbull