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
977 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:

converting a chart from 2003 version to 2007
I had a document with numerous charts in it in 2003 version. when I attempt to open the sheet with 2007 version, I get an error message and then it closes my excel. Is there a quick fix for this or perhaps a setting I need to change? Do you get any kind of error message? With Excel closed, empty your temp directory. There might be a file there left over from before a previous Excel crash, that's still causing problems. - Jon ------- Jon Peltier Peltier Technical Services, Inc. 774-275-0064 http://peltiertech.com/ On 4/6/2010 1:37 PM, Deb J wrote: > I had...

rows to be copied to next sheet based on value of particulkar cell
hi, What I want to do in my workbook is data in rows copied to next sheet based data in Column F (date) of row. The copied data must be in copied to same row in next sheet. Column F contain date. If date is same month as current date. date should not be copied to next sheet but if date is not the same as current month then the whole row should be copied to next sheet. Every sheet correspond to one monthm so total of 12 sheets. tabylee -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/201001/1 ...

XY Chart Label
Need the help up of the Excel gurus.. I'm using Rob Bovey's XY Chart labeler to label a Red, Yellow, and Green XY chart. By the way...this add-in is great!!! The data for the charts are built from dynamic named ranges for both X and Y. I have a helper column for the labels that is a dynamic named range as well. All data is linked to an MS Access db. Here's what I'm trying to do: The data for the charts will change whenever I load the file with new data and was wondering if anyone knew how to create a macro to call up the Add In and place the dynamic named range in th...

sql request to return port number
Hello, I am looking a for request (probably on sys views or tables) that would return the port number on which sql server is listening to (default is 1433). Thanks for your help Hi You can find this info in ERROR.LOG file "Wilfrid" <grille11@yahoo.com> wrote in message news:4b03f777$0$19300$426a34cc@news.free.fr... > Hello, > > I am looking a for request (probably on sys views or tables) that would > return the port number on which sql server is listening to (default is > 1433). > > Thanks for your help > Hi, ...

Chart title = cell contects
How do I set my chart title to the contents of a cell, and then what ever the cell contents are, the chart title is changed to automatically? Dean -- dkso@ntlworld.com http://www.dkso.co.uk/ http://homepage.ntlworld.com/dkso Hi, Here is an explanation, http://www.andypope.info/tips/tip001.htm Cheers Andy Dkso wrote: > How do I set my chart title to the contents of a cell, and then what ever > the cell contents are, the chart title is changed to automatically? > > Dean -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Any, Thanks, just what I was after ho...

Tools/Options/Charts-Active cells is dimmed. Want to select leave
I tried using #N/A in equation IF(B7=0,#N/A,B7) as to not show zeros in chart, but still showed zeros. When I went to Tools/Options/Charts, the Active Cells area was dimmed and I could not select "leave gaps". Any suggestions? Thanks, Hi, Was the chart selected when you did Tools>Options ? Cheers Andy teds wrote: > I tried using #N/A in equation IF(B7=0,#N/A,B7) as to not show zeros in > chart, but still showed zeros. When I went to Tools/Options/Charts, the > Active Cells area was dimmed and I could not select "leave gaps". Any > suggestions? &...

Protocol: SMTP, Server Response: '250 HELP', Port: 25, Secure(SSL): Yes, Server Error: 250, Error Number: 0x800CCC7D
I cant receive or send emails in windows mail. What do I have todo? Please copy and paste the error message into the body of your post . . . not the "Subject" line - it got truncated. "valeria migelson" <valemigelson@gmail.com> wrote in message news:eW$sk6#kKHA.4772@TK2MSFTNGP04.phx.gbl... > I cant receive or send emails in windows mail. What do I have todo? Judging by the fragment of the error message you have in the subject = line, it looks like you have the wrong settings for Gmail. Use these: http://mail.google.com/support/bin/answer.py?answ...

Charts have X axis at the top?
I just tried to create a couple of charts and every one places the X Axis at the top when showing expenses. That is the numbers display in the lower right quadrant. The worksheet displays expenses as a negative value and this is what I want... numbers in parens. Is there a way to show my expenses in the upper right quadrant without affecting the display of these figures? Thanks, Richard Richard, Negative values go downward from the X axis. If there are no positive numbers, then the X axis will be at the top. If you want the Y axis to get more negative as it goes upward, you can do th...

Numbering Lines and Paragraphs
I'm using MS 2007. I'd like to number the lines of a doc, but restart the numbering with each new paragraph. The only options I see are: Continuous Restart Each Page Restart Each Section Suppress for Current Paragraph Is there any way I can change the settings, short of making every paragraph it's own section? (I'm doing some editing for a project and we use shorthand to reference where to make changes, such as: P10 L15 [paragraph 10 line 15]) Thanks! To clarify... I also need the line numbering to start new at each paragraph so in the example (...

Is there a way to split a sheet so columns can be different widths?
I have a bunch of worksheets that have a top part and a bottom part. The top part contains settings for various parmeters. The bottom part is a table showing calculations based on those parameters. The problem is that the parameters often need different column widths than the table. Is there some way to split a sheet into a top part and a bottom part were I can adjust the column widths independently? Column widths and row heights are properties of the entire column and row and cannot be changed. I hesitate to mention "merged cells" due to the problems they can cause. ...

Filter based on formatting
In an Autofilter, is it possible to select the cells which have a specific background color (say, yellow)? -- Regards, Fred Please reply to newsgroup, not e-mail Fred Not without some VBA Function help. See Chip Pearson's pages on colors. http://www.cpearson.com/excel/colors.htm Using a helper column and Chip's CellColorIndex Function you could then Autofilter on Color Index number returned by a formula in the helper column. Gord Dibben Excel MVP On Tue, 2 Mar 2004 18:55:39 -0600, "Fred Smith" <fredsmith99@yahoo.com> wrote: >In an Autofilter, is it possi...

Define Chart Name??? #2
hi debra thank you very much for your help... i managed to figure out th problem... it was due to the fields not updated properly... there' still old fields hence misleading me to think that your code to sho all the items were not working... i have yet to try the code provide by you to remove all the old fields but i'm pretty sure it woul work... once again thank you very much for your help... cheer -- hc ----------------------------------------------------------------------- hce's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=351 View this thread: ht...

Icon label in Tasks
Outlook 2003 The first item in the label list for my Tasks is "Icon" which appears as a blank piece of paper. When I setup a task an icon shows in that column as a clipboard with a red check on it. Exactly what is this icon trying to tell me? I've not found a way to change anything in the task that affects this icon in the "Icon" column. Thanks! That is the standard task icon. It just means it’s a simple task, not recurring, not assigned. http://www.outlook-tips.net/howto/icons_tasks.htm -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://ww...

Auto Numbering 09-13-05
I would like to create an auto number field to be used as a customer number. I would have thought this would be a common requirement. Is this possible. thanks in advance Todd this is a common issue and the way to achieve it is to use post callout code which finds the last number used and then calls back into the crm application layer to update the record. here is some example code for crm 1.2 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmbscrm1_2/html/mbs_crmpostcalloutsv1d2.asp "Tango" <Tango@discussions.microsoft.com> wrote in message news:89FA...

how do i save chart formats as default
I can make a presentation in minutes if only i could keep formatting on my charts and graphs. As it is, it takes me about 4 hours because i have to change fonts, colors, bars, etc... In Excel help there is a topic entitled: "Create, share, or delete your own chart types" Does that help? -- David Biddulph "jalooney" <jalooney@discussions.microsoft.com> wrote in message news:22CD6557-6675-4A97-BE09-5D1DE3933927@microsoft.com... >I can make a presentation in minutes if only i could keep formatting on my > charts and graphs. As it is, it takes me about 4 hou...

Incorrect date in line chart in Excel.
I'm having difficulty with a line chart. I've got two ranges of numbers of two types of calls (from 1972 through 2004) that goes up from 50 to 7700. My chart's Y axis has automatically decided to show from 0 to 16,000. On the chart itself, my data placement starts at about 6,000 (for both 24 “F” calls and 649 “M” calls in the year 1972) and goes up to a point above 14,000 (for a figure that should only be just above 7000). I've tried to change the scale of my Y axis, but when I am able to so (2 out of 5 times) my data then goes off the chart. So I've looked at m...

dot plot chart+chart wizard
Can one make a dot plot chart using chart wizard. This chart will have 40 data points. The Y axis will be split in the middle with the upper half being "+" and the lower half being "-". The median line on Y will be "0". The graph will demostrate the differences from one number compared to a second number. Thanks If you mean a dot plot as described in this page and the links it contains: http://peltiertech.com/Excel/Charts/DotPlot.html then no, you can't make a dot plot directly from the chart wizard, but the page above includes a link to a utility...

possible to check a cell for both even number and positive number.....
I have found a workable formula to check for even numbers but I am not being sucessful in having it also check for positive numbers..... I am using the following data verification statment to check for even numbers and it works fine..... =MOD(G17,2)=0 problem is that the number they are supposed to enter has to be a positive number because negative numbers are invalid data in this application. Any help would be appreciated. Hi Try this one. =IF(AND(MOD(G17,2)=0,G17>0),"True","") HTH John "M G Henry" <mr_mikehenry007@hotmail.com> wrote in message new...

Maintain the same number in a spreadsheet
I use a spreadsheet to calculate my contract price for a bid. I would like to maintain the same price amount with a formula that would automatically adjust the percentage of profit up or down when a direct cost in the spreadsheet amount is changed. For example; if I have a bid price of $1000 with a 10% profit ($100)and the price of an item that is part of the direct cost goes from $50 to $60 the percentage woud autamatically adjust my profit from 10% to 9%, decreasing my profit to $90, accounting for the additional $10 of direct cost, but the bid price would remain at $1000. On Mon, 29 Mar 201...

excel and scientific charts
I would like to plot professional scattered charts in excel like in gnuplot. I want to plot almost 500-600 numeric datas but the graphic results are not very beautiful. I've tried to change axis and scale but is not very good. Is there the possibility to obtain better charts? a plug-in? vba? thankx. For possibilites see http://groups.google.com/groups? selm=MPG.17580d283c5b24e598a63e%40msnews.microsoft.com -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <W2HXb.316276$_P...

Using concatenate and Indirect to reference a range from a remote workbook
Hi all I am trying to compare values in two ranges, one from the active workbook and the other from a different remote workbook. I am using the Vlookup with the table array field being populated from a range of concatenated cells. The first one defines the path of the remote workbook, the second defines the name of the workbook (because the workbook name changes each week) and the third is the range name. So the Table array part of the Vlookup looks like ths:- INDIRECT(CONCATENATE(File_path,"Forecast_Chk_WK_",Cur_Week-1,".xls!FCast_tot")) Now prior to adding the File_pa...

if the numbers in sequence
Hi All, I have a 7 cells in a row like A1,A2,A3...A7. Each cell contains numbers from 1 to 13. Is there any way to make a formula to calculate if there is any sequence of 5 numbers. example if A1=5,A2=7,A3=8,A4=9,A5=10,A6=3,A7=4 not sequence A1=6,A2=7,A3=8,A4=9,A5=10,A6=3,A7=4 Try this =3DIF(AND(A2>A1,(A2:A5)-(A1:A4)=3D1),"Sequence","Not Sequence") Not just enter, Use Ctrl + Shift + Enter On Nov 15, 1:06=A0pm, baha <bah...@gmail.com> wrote: > Hi All, > I have a 7 cells in a row like A1,A2,A3...A7. Each cell contains > numbers f...

How do I convert nnnnnnnnnn to nnn nnn nnnn (Telephone numbers)?
Using Win XP, Excel 2003. I have hundreds of telephone numbers (exported from Outlook) in a worksheet column, some local, some international, all formatted as "General" 1) A formula or function to convert the following?: nnn nnn nnnn from nnnnnnnnnn also nnn nnn nnn nnnn from nnnnnnnnnnnnn also nn nnn nnn nnnn from nnnnnnnnnnnn etc, etc In essence each telephone number is to be converted to a groups of numbers (with a space between each group) with the last group contains 4 digits, with preceding groups (upto four) containing 3 digits and the first group either 1,2 or 3 digits.-...

How do I stop last number from changing to a 0 in excel
I have a worksheet that has one column that has account numbers. If I enter 16 or more digits the last numbers change to a 0. I have set, cell format to numbers with no decimals. Try setting cell format to text before entering or use an apostrophe then enter the number HTH Regards, Howard "dan12" <dan12@discussions.microsoft.com> wrote in message news:426335E4-F08B-4C23-B6E2-EB45F36101F5@microsoft.com... >I have a worksheet that has one column that has account numbers. If I enter > 16 or more digits the last numbers change to a 0. I have set, cell format > to &...

Two charts, one on top with transparent chart area
I would like to take two charts, placing one on top of the other with the top chart having a transparent chart area (As though I were laying one overhead transparency on top of the other). I can't seem to find a way to make the chart area transparent. Selecting "None" for the chart border and area leaves a white background. Any ideas? Thanks! Nyle Elison nyle_elison@byu.edu You should also format the plot area with None for area and borders. Jon Peltier has some code to align charts: http://www.geocities.com/jonpeltier/Excel/Charts/AlignCharts.html Nyle Elison wro...