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