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
0
zentropic (23)
5/20/2005 6:21:47 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
1023 Views

Similar Articles

[PageSpeed] 27

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
0
BJ (832)
5/20/2005 6:18:00 PM
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
0
zentropic (23)
5/20/2005 6:54:04 PM
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
0
5/21/2005 4:54:21 PM
Reply:

Similar Artilces: