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
941 Views

Similar Articles

[PageSpeed] 51

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:

Chart Series Legend
This is a multi-part message in MIME format. ------=_NextPart_000_0036_01C45E93.195F0E80 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I have a chart that shows fives series. Three of the series are not = being used as you'd imagine. Is it possible to remove the 3 series from showing in the series legend? = The series ARE needed in the chart, but I just dont' want them to be = shown in the legend. Thanks, Jay Gustafson ------=_NextPart_000_0036_01C45E93.195F0E80 Content-Type: text/html; charset="iso-8859-1"...

chart disappears after inserting another chart into slide
When i tried to insert another chart from excel to my powerpoint slide, (using copy & paste function), the other chart that was already in the slide disappears and I can't find it anywhere in my powerpoint... How do i search for it or rather why does this happen? I guess you're using PPT 2002 (also known as PPT XP) and you have this Autolayout feature turned on. I think it's in Tools | Autocorrect, but I can't check it right now to be sure. -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2007? http://www.echosvoice.com/2007.htm Fixing ...

Autofill of row or column
Greetings, Let's say I have a min, a max, and an increment, all decimal values. I want to autofill or autopopulate a row or column -- without manually dragging cels -- over the range [min,max] incrementing by the increment value each cell. Can this be done somehow? Also, what if you don't know how many cells you will need initially (you return some variable integer from a calculation)--can you tell Excel to fill only that number of cells each time? For example, let's say I compute some number and get a 7 one time and a 10 the next, and I want 7 cells filled with data and then ...

Absolute Values and Column Insertions
I've created a formula in column A that adds up some cells in column D. Ex: =sum(D3:D6). If I insert one column in front of column D, the formula changes to =sum(E3:E6). This happens even if I change the formula to =sum($D$3:$D$6). I also tried creating a range name called TEST referring to the original $D$3:$D$6 range and the column insertion changed the TEST range definition as well. Is there a way to keep a formula from NOT adjusting to column insertions or deletions? Thanks in advance! Please don't multi-post See answer in other newsgroup -- Kind regards, Niek Otten Mic...

auto numbering in a query
Hi all, I need to build a query that have in a field auto numbering and in a certain formating for example: 0001 0002 ....... 0012 ....... is it posible? Is this to produce data that will be viewed in a report, by any chance? "thread" <yaniv.dg@gmail.com> wrote in message news:1175794888.467011.108490@w1g2000hsg.googlegroups.com... > Hi all, > I need to build a query that have in a field auto numbering and in a > certain formating > for example: > 0001 > 0002 > ...... > 0012 > ...... > is it posible? > On Apr 5, 2:42 pm, "BruceM&q...

Using icon sets with relative references
I am using Excel 2007 in the Vista OS. I am trying to use conditional formatting with an icon set in a column but Excel will not allow relative references. Example: A1=10 min B1=20 max C1=30 oh D1=a formula { =IF(C1=0,B1,(B1-C1)) } The condtional formatting of D1 would be: if the value showing in D1 is >B1 the cell would show a red 'X', if the value showing in D1 is <A1 the cell would show a green 'check mark', and if the value showing in D1 is between the values in A1 and B1 it would show a yellow 'exclamation point'. I can get the D1 ...

Creating a chart of overlaid area circles?
Can anyone help? I am trying to create a chart that contrasts size by laying 3 different sized circles on top of each other (so it will basically look like a shooting target). I know i can represent this data in other ways but this will have greatest impact for the purpose. Can Excel do this? And if so, how? - Please bear in mind I am not very techy so the simpler the better! Thanks very much Hi, You could use a bubble chart. That takes 3 parameters x,y and size. keep the x and y constant and adjust the size. Cheers Andy m.nutt1 wrote: > Can anyone help? > > I am trying to...

Chart, lines drop to zero when no data entered
Have several charts that get the data from a master data sheet which gets its data from several other sheets, thus a linked formula is involved. X axis is for Jan - Dec. I only thus far have data for Jan - May. The lines on the charts are fine for Jan - May, but since there is no data in June - Dec, the line drops to zero. It make the production chart look like the person suddenly stopped working. I realize I can erase the formulas but this is not acceptable in that several people, not excel proficient, will be updating their own charts and printing them. I want the line to sto...

How to average a column, but exclude zero AND negative values?
I used the formula of =AVG(IF(A1:A10)<>0,A1:A10)) to exclude just the zero values, but what about zero AND Negative? Thanks!! =AVerage(IF(A1:A10)>0,A1:A10)) Still array entered. KMHarpe wrote: > > I used the formula of =AVG(IF(A1:A10)<>0,A1:A10)) to exclude just the zero > values, but what about zero AND Negative? > > Thanks!! -- Dave Peterson ...

update chart automatically
I need to know how to update a chart automatically so it wil incorporate new months data as it is entered into a log. I have a data log with a complete list of the required data in column and the dates listed for each. When a new month begins I would like to plot data for it automaticall in a pre existing bar graph as it is entered into the log. My goal is to make this a user friendly program so I don't have to b the only one to know how to use it. Another problem I have run into is my graphs use several series to sho the data, I would like each month to contain data from each se...

SSL & Forms based auth messed up rich client version
Okay, I'm about ready to scrap OWA entirely. I had OWA up and running perfectly with no security, however external clients could not log into their mail boxes. They would get the login screen but their credentials were not being accepted even though I know they were typing them in correctly. So I decided to try SSL and Forms based Auth. Now when I try to login using IE6 sp1 to the premium client version about half the site loads, but stops, with a "loading" message where the viewing pane should be. I'm running exch 2003 on win2k3, for both the back end, and front end s...

Chart drawing problem in Excel 2003
Over the last few months I have had the same problem in excel with all charts where I've used a two colour fill effect for either a data series or background. When I use this feature the entire chart is only viewable in black and white and excel takes a long time to actually draw the chart. However if I print the chart or even copy and paste into excel all the colours are as they should be. The mpeg moving shows how excel draws an example chart when the chart sheet is activated http://www.paperlessbooks.co.uk/barchart_example.avi and to see how excel prints to pdf see the pdf belo...

text conversion to number on select query
Hi, In a Select Query I'm joining 2 tables by Item ID (unique value, similar to Social Security Number) but 1 table created by IT has Item ID as a "number" value and the other table has it as a "text" value. How can I in a Select Query, create a formula that can either have the text as a number value and vice versa so i can link the 2 without getting "type mismatch in expression." I think I can use Cdbl Value or something like that in the formula but not sure. Thanks! "inspirz" wrote:subed going to jail > Hi, > ...

Exchange 5.5 Microsoft Knowledge Base Article: 306308
Hi, I am having a problem with Exchange Internet mail service, I recive all errors that the knowlege base article lists, however I am new to exchange and I am unsure as to how to check that the account has permission to logon to the information store. Can someone please help. Thanks in advance. If you go into your services applet in NT 4 go to Information Store servie and then Startup button and look at the bottom at Log On As and see what account you are using. For Win2k, go to the properties of the Information Store and the Log On tab. Hope this helps -- Melissa Travers, MCSE Micr...

print column labels with one row of data at a time.
I am a teacher. I have created my gradebook in excel. (I don't have access). I have 7 worksheets (one for each class that I teach). The worksheets are not exactly the same. I would like to be able to print out the top two rows (column labels) with each students data (row). This way the student can see row 1 (name of assignment), row 2 (points), their row (their personal data for each assignment). Any help?? Go to File > Page Setup > Sheet Then in the "Rows to repeat at top" highlight the label headings row(s). HTH -- _______________________ Naz, London...

Calculated Columns Help
FIND/SEARCH functions both return #DEFAULT! if string is not found. If it is not found, I'd like to display a different value (one of the other columns). All my attempts to check if the calculated column = "#DEFAULT!" fail. It is as if it is not really a string. How can I test for #DEFAULT! value? ...

How to Link to the next blank row after the last entry in a column?
Cell BM6 Sheet1, has a formula that gives me a numeric total. The total belongs in Column D1 Sheet2 after the last entry, and there will be cells that will remain blank in that column before the last entry. Sheet1 is a work sheet, which will be saved with a new file name at the end of the month after the totals have been sent to Sheet2. Sheet2 is a permanent, ongoing record. The numbers on original Sheet1 will then be erased and the worksheet reused for the new month. :confused: How do I get the values into the appropriate row of Sheet2 and have them remain there when I reuse the workshee...

automatically numbering rows
I wish to have rows automatically numbered in column "A" according to the content in column "B". If column "B" has text or numbers in a cell I do not want to number that row in column "A". If column "B" is blank in a cell I want to number that row in column "A". I found the following formula that works the opposite of what I want "=IF(B1<>"",COUNTA($B$1:B1)&".","")" . What would the formula be if I wanted to numerically count the blank cells in "B" and skip the conte...

Setting column width on different computers
I have a program which checks workbooks and corrects those errors which it can correct. One check is on the column widths: Column A should be 1.43 characters wide and column G should be 8.86 characters wide. This works fine on 99% of the computers where it is used but we have one or two where if you change the width of column A to 1.43 it goes to 1.44, and for column G if you set it to 8.86 it goes to 8.89. This means that the next time the check is run, the workbook is deemed a failure. The difference appears to be the screen resolution: on the computers where it works the...

page numbers disappear
Page numbers disappear after page 10 whenever I do a booklet. On Sun, 19 Feb 2006 02:03:26 +0000, 4Av wrote (in article <C5C8808C-2FA7-4019-93DF-24FA9FAB21C3@microsoft.com>): > Page numbers disappear after page 10 whenever I do a booklet. And we're supposed to guess what version you're running are we...? We're not psychic - please elaborate on 'disappear' - do they just vanish as soon as you enter them, or is this resuming a saved document, or what? Or do they not appear at all... The devil's in the details... ;o) Do you have a Master page? Have you ch...

Serial Number Management
Recently i was approached about resolving a problem we are havin logging in laptops we receive on a daily basis. Because of the larg quanities it is hard logging in each laptop by hand using their seria numbers. Is it possible to specify on sheet 1 the upc number and descriptio of each notebook (product) Sheet 2 Contains the serial numbers scanne using a usb scanner or is there a limitation in excel that will stop m from using a usb scanner? Sheet 3 will contain a log sheet consisting o date in and out box number serial number etc. enk0 -- enk0 ------------------------------------------...

APA Template
I am new at using the APA template in Word 2007, the first few papers it was pretty easy , but now my last paper after I saved and submitted it, the header and page numbers dissapear. 2 questions , #1 how can i delete pages on a template without messing up the APA format , # 2 why when I save my document ,the header and page number dissapear. ( i dont need all those extra pages at the end after references , and i want to erase the preprinted text explaining the APA format), what is the best way to do this, any word 2007 expert using APA template please help. -- Old new college stud...

Copy/Paste with Hidden Rows/Columns Excel 2007
I am working with rather large & detailed spreadsheets. I have hidden rows and columns with supporting details that feed into the viewable results (formulas). I need to take the “zipped up” data and copy/paste to a new worksheet. When I paste the data, it appears with all of the hidden columns and rows visible one again. Is there any way to paste the zipped up data without the reappearance of the previously hidden rows and columns? Can you paste a link without all of the hidden data reappearing? Select range to copy which includes hidden rows and columns. F5>Special...

Cell Reference for the Color of a cell?
Hello to all you beautiful Excel users hope you are grand! Reason I write today is this: Is it possible to create a Cell Reference to another Cell so tha information such as the Border of the Cell Border OR the Color of i will transfer to another Cell? For instance: Cell A2 is red and has an underline border and I would like to create reference that makes D4 red and with an underline without having to us the tool bar. Then if I change A2 to green D4 will turn green. Please respond. Thanks Ti -- Message posted from http://www.ExcelForum.com You can't do this kind of thing with a f...

How can i create a 2-D stacked column within a 4 series stock char
I am trying to create an excel chart that combines the features of a 100% 2-D stacked column (for comparison of 2 variables) and the 4 series stock chart. I would desire to have my ideal chart being a 5-series stock chart with the data variables arranged as 'Volume1-Volume2 (stacked up as in 2-D column)-High-Low-Close'. This is slightly different from the current 5-series stock chart available in the excel spreadsheet. Can anybody help please? Thank you. Sincerely, Samuel See http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html http://peltiertech.com/Utility/ClusterStackU...