Thread: chart label reference based on the column number

In a worksheet with an embedded chart, I have a cell, S4, where I enter the
number of the column I want to chart (these numbers are listed as labels in
cells A2:R2).

I can't figure out how to translate the number in S4 into the corresponding
column letter. For example, if S4 contains "3", the chart title should be
=$C$2.

I think working with absolute references, R1C1, might be easier here, but
somehow what I've tried, didn't work (the entire spreadsheet is based on
relative addresses (A1).

z.entropic

Thanks; the INDEX worksheet function is something I haven't had to use
before. I'll read up more in it.

I git it to work with
=INDIRECT("R2C"&S7,)
as well.

z.entropic

p.s. what I meant in my orginal post is the referencing STYLE (R1C1 vs. A1),
not relative or absolute references (A1 vs $A$1).

"bj" wrote:

> in a cell (Z100) put
> =index(A2:G2,1,S4,1)
> Change G2 to whatever you need
> Select the graph title block and in the formula section enter =Z100
> (or whatever cell you want to use.
>
> "z.entropic" wrote:
>
> > In a worksheet with an embedded chart, I have a cell, S4, where I enter the
> > number of the column I want to chart (these numbers are listed as labels in
> > cells A2:R2).
> >
> > I can't figure out how to translate the number in S4 into the corresponding
> > column letter. For example, if S4 contains "3", the chart title should be
> > =$C$2.
> >
> > I think working with absolute references, R1C1, might be easier here, but
> > somehow what I've tried, didn't work (the entire spreadsheet is based on
> > relative addresses (A1).
> >
> > z.entropic

You could also try the OFFSET function.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


z.entropic wrote:
> Thanks; the INDEX worksheet function is something I haven't had to use
> before. I'll read up more in it.
>
> I git it to work with
> =INDIRECT("R2C"&S7,)
> as well.
>
> z.entropic
>
> p.s. what I meant in my orginal post is the referencing STYLE (R1C1 vs. A1),
> not relative or absolute references (A1 vs $A$1).
>
> "bj" wrote:
>
>
>>in a cell (Z100) put
>>=index(A2:G2,1,S4,1)
>>Change G2 to whatever you need
>>Select the graph title block and in the formula section enter =Z100
>>(or whatever cell you want to use.
>>
>>"z.entropic" wrote:
>>
>>
>>>In a worksheet with an embedded chart, I have a cell, S4, where I enter the
>>>number of the column I want to chart (these numbers are listed as labels in
>>>cells A2:R2).
>>>
>>>I can't figure out how to translate the number in S4 into the corresponding
>>>column letter. For example, if S4 contains "3", the chart title should be
>>>=$C$2.
>>>
>>>I think working with absolute references, R1C1, might be easier here, but
>>>somehow what I've tried, didn't work (the entire spreadsheet is based on
>>>relative addresses (A1).
>>>
>>>z.entropic