Trying to use a cell reference value in X Axis value of Chart

[Ronanm]

Hi

I'm trying to use a cell reference value in the X Axis labels of Chart.
For example the static axis labels reference would be
=Student!$B$26:$R$26

However instead of it being $B$26, I need it to be $B with whatever Row number my named range "ColumnNumber" returns.

Many thanks

Ronan

 

[jim may]

I set up a named range on Sheet1 for cells or range D4:D8 -- named "ColumnNumber"
then in the immediate window, I entered:

? Activesheet.Range("ColumnNumber").row
4 << The first row of the named range "ColumnNumber"

 

[GlennUK]

Use the OFFSET function in a defined name, like =OFFSET(Student!$B$1,ColumnNumber-1,0,1,17), and reference the defined name in the axis labels reference as =SheetName!DefName.

 

[jim may]

Short sample... maybe helpful....

Sub Macro1()
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Select
    RNum = Range("ColumnNumber").Row
    ActiveChart.SeriesCollection(1).Formula = _
        "=SERIES(,Student!$C$" & RNum & ":$C$10,Student!$D$" & RNum & ":$D$10,1)"
End Sub

 

[Ronanm]

Mmmn thanks. Sorry, I made a slight error

If my ColumnNumber named range returns 12, I need the Axis Labels to be P26:R26 instead of B26:R26. Which would show Years (labels) from 2007 to 2010, instead of 1994 to 2010.

Obviously in my Charts X Axis I can't simply have
=Students!$ColumnNumber$26:$R$26 which is a shame...

Many thanks

Ronan

 

[Ronanm]

Mmmn. Many thanks, but ideally would like to steer clear of code... Life is difficult enough as it is )

 

[GlennUK]

Mmmn thanks. Sorry, I made a slight error

If my ColumnNumber named range returns 12, I need the Axis Labels to be P26:R26 instead of B26:R26. Which would show Years (labels) from 2007 to 2010, instead of 1994 to 2010.

Obviously in my Charts X Axis I can't simply have
=Students!$ColumnNumber$26:$R$26 which is a shame...

Many thanks

Ronan

=OFFSET(Student!$B$1,0,ColumnNumber+2,1,ColumnNumber-9)

 

[Ronanm]

Hi Thanks for the input.
I have made my changes (tried to attach a sample file, but couldn't so went to http://www.excelforum.com)

Two issues...
One is the the labels always appear at the start of the X axis, so I end up with 2008, 2009, 2010 at the start, but the columns of data at the end...
Secondly is that ideally I would like the Chart to expand and contract (which would also fix the first problem), so that if there were only 3 years, then it would only be 3 Years wide...

Many thanks

Ronan

 

[GlennUK]

You have to adjust all your data to match the labels. Use OFFSETs for all of it.