Setting Chart X and Y values indirectly

I would like to set X-Values in my chart in a cell of a worksheet, so that 
when the range of my x-values changes I will not have to go into the wizard 
and type it again.

For example, I want to use the following X-values in my chart:

=DATA!$I$2:$I$20

But instead of typing the above in the X Values: field in the 'Source Data / 
Series' wizard, I would like to type it, say, in cell $A$1 of 'Sheet1', and 
then type in the X Values field in the 'Source Data / Series' wizard the 
following:

=Sheet1!$A$1

I tried it and it does not work.  Can you please let me know if there is a 
way to achieve what I am trying to do?

Thanks.
0
Manos (6)
10/26/2007 1:44:01 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
775 Views

Similar Articles

[PageSpeed] 21

Define a name (Insert menu > Names > Define) that uses the definition of the 
range:

Name: MyXRange
Refers To:
=DATA!$I$2:$I$20

Then in the source data dialog, you can enter =DATA!MyXRange in the X values 
field. You can use formulas to make the definition of MyXRange change 
according to how the data itself changes. This technique is referred to as 
'Dynamic Charting'. See some examples and links here:

http://peltiertech.com/Excel/Charts/Dynamics.html
http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html

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


"Manos" <Manos@discussions.microsoft.com> wrote in message 
news:88EB4676-D6B9-40E7-9561-4604C12C6255@microsoft.com...
>I would like to set X-Values in my chart in a cell of a worksheet, so that
> when the range of my x-values changes I will not have to go into the 
> wizard
> and type it again.
>
> For example, I want to use the following X-values in my chart:
>
> =DATA!$I$2:$I$20
>
> But instead of typing the above in the X Values: field in the 'Source Data 
> /
> Series' wizard, I would like to type it, say, in cell $A$1 of 'Sheet1', 
> and
> then type in the X Values field in the 'Source Data / Series' wizard the
> following:
>
> =Sheet1!$A$1
>
> I tried it and it does not work.  Can you please let me know if there is a
> way to achieve what I am trying to do?
>
> Thanks. 


0
jonxlmvpNO (4558)
10/27/2007 10:06:31 PM
John,

Thank you!  I will try your suggestion.

Manos

"Jon Peltier" wrote:

> Define a name (Insert menu > Names > Define) that uses the definition of the 
> range:
> 
> Name: MyXRange
> Refers To:
> =DATA!$I$2:$I$20
> 
> Then in the source data dialog, you can enter =DATA!MyXRange in the X values 
> field. You can use formulas to make the definition of MyXRange change 
> according to how the data itself changes. This technique is referred to as 
> 'Dynamic Charting'. See some examples and links here:
> 
> http://peltiertech.com/Excel/Charts/Dynamics.html
> http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
> 
> 
> "Manos" <Manos@discussions.microsoft.com> wrote in message 
> news:88EB4676-D6B9-40E7-9561-4604C12C6255@microsoft.com...
> >I would like to set X-Values in my chart in a cell of a worksheet, so that
> > when the range of my x-values changes I will not have to go into the 
> > wizard
> > and type it again.
> >
> > For example, I want to use the following X-values in my chart:
> >
> > =DATA!$I$2:$I$20
> >
> > But instead of typing the above in the X Values: field in the 'Source Data 
> > /
> > Series' wizard, I would like to type it, say, in cell $A$1 of 'Sheet1', 
> > and
> > then type in the X Values field in the 'Source Data / Series' wizard the
> > following:
> >
> > =Sheet1!$A$1
> >
> > I tried it and it does not work.  Can you please let me know if there is a
> > way to achieve what I am trying to do?
> >
> > Thanks. 
> 
> 
> 
0
Manos (6)
10/29/2007 7:39:01 PM
Reply:

Similar Artilces:

AfxHookWindowCreate fails and createEx returns getlasterror value as Zero and does not give a meaningful return error value
Hi, My application crashes after running for a long time, when analyze the cause, i found that CreateEx is causing the problem. In this AfxHookWindowCreate is failing. when i tried to get the error code for the createEx, it returns getlasterror value as Zero and does not give a meaningful return error value. Can any one please tell why AfxHookWindowCreate can fail..The window iam creating is a child window and having attributes as WS_EX_TRANSPARENT. Note: It fails after running for a long time. Not in the first atempt itself. >My application crashes after running for a long time, when ...

Help
Hope someone can see the error of my way. I have a workbook saves as a template. Each week I receive a new excel file (data dump) from payroll timesheets of all the employee's hours charged to the different job codes and projects they worked on for the week. With this file open, I run the below macro. I only use two colunms, job numbers and hours from the original file. I copy the two columns, then open the template, paste the data into the newly created file, input the week ending date in a cell (that cell is referenced in the chart title), then do a save, then ask for it to calculat...

How to assign datalabels to a scatter chart, obtaining the labels.
How to assign data labels to the points of an xy scatter diagram (excell 2003)? The labels are to be fetched from a column in the data sheet, but not from one of the columns containing the x- and y-values. This should be possible. How can I do this? Thanks in advance. You can use Rob Bovey's XY Chart Labeler to add labels. It's a free add-in that you can download from his web site: http://www.appspro.com/Utilities/ChartLabeler.htm Jos Koot wrote: > How to assign data labels to the points of an xy scatter diagram (excell > 2003)? The labels are to be fetched from a col...

office v. x updates 10.1.6 and or 10.1.9
I was trying to update my office x with the update 10.1.6 and it would allow me to start the update, but then when it was installing the visual basic libraries, it would stop and give me the error "error creating folder" "1008:9,-43 file not found". In addition, when I tried to use the 10.1.9 update, it said that I do not have any qualifying products on my computer. I know that I have office v. x installed, because update 10.1.6 recognized it. I think that this is a folder permission issue, but I do not know which folders I need to change. If at worst, I would like to get e...

Simple
Guys, I am writing a very basic proc to return a list of court types. The developers have requested that I include a bit that can toggle whether or not I include the 'All' option in the result set. I have this at the moment: CREATE PROC dbo.CourtJurisdiction_lst @show_all_option BIT = 1 AS SELECT long_name, court_type_id FROM ( SELECT 'All' AS long_name, -1 as Court_Type_id, 0 as OrderId UNION SELECT long_name, court_type_id, 1 as orderId FROM dbo.z_court_type ) a ORDER BY a.orderId, a.long_name Based on the @show_all_option bit, is ...

Problem with .NET Security Settings
Hello, I have a problem with security-settings in a .NET Application. The application has a strong name and is full trusted ("Trust an assembly" - Full Trust), ..NET Security is set "Local Intranet - medium trust". The application ist installed on a network-drive. Working-Directory and Configuration-Files are also on different network-shares. The application uses intensively XSLT and XML. The following XSL-Transformation-process fails (without errors !): Base-Document XML X is stored on network-folder A. Transformation-Stylesheet XSL Z is stored on Networkfolder B ...

pivot chart mystery fields
I've been given the task to reverse engineer an excel pivot chart that an ex-employee created. When i look at the data source it pulls in all the fields from a particular table from an access database. However, once it gets to Step 3, and i select Layout, all of a sudden there are these extra fields that have appeared from no where! The data in these extra fields is used as grouping for the pivot chart. There is nothing in the query telling it to create extra fields, it just pulls everything from the table. When i told the chart to list all formulas (thinking they might be calcu...

Change setting for mailing attachments
Hello, I'm running GP 7.5 and Acrobat 5.0 Distiller. When I send to Mail Recipient as PDF, it works fine, and attaches a pdf to a new mail message, however, Acrobat ALWAYS opens. I've turned off all imaginable settings in acrobat and even changed the default pdf file association with acrobat 7 reader and still, whatever that file association is linked to, GP will run acrobat and show a preview. Any thoughts? ...

Publisher: set up an 11 X 17 tabloid with facing pages
I'm trying to create a tabloid which is 11" tall and 17" wide (folded in half--with facing pages). Please help me set it up. This based on Publisher 2000 but it should get you there. File Print setup set the paper size to 11"x17" OK now File Page Setup Special fold dot Book setup Verify the page size is 8.5"x11" (after Landscape is selected) Landscape Next it will ask you if you want groups of 4 pages, yes. This should get you going. -- Don Vancouver, USA "newsletterlori" <newsletterlori@discussions.micros...

VBA error: Unable to set the Values property of the Series class
Excel 2003 SP1 Trying to create a chart using VBA. The following two subs are edited a bit after being created by the macro recorder. The send sub errs on this line: ActiveChart.SeriesCollection(2).Values = "=Sheet1!R12C3:R22C3" With the error: Run-time error '1004': Unable to set the Values property of the Series class Column 3 is just a series of numbers. Strangely, the first ActiveChart.SeriesCollection(1).Values line works OK, but then the 2nd fails. I can't figure it out... Sub area_chart() ' ' area_chart Macro ' ' Range("A1:C72"...

Adding a value to blank cells
I have a spreadsheet with a number of blank cells in a column, I want to fill these cells with a another cell value from a different column, the value will need to change every time a non blank cell is reached, as will the data to be put in to the next section of blank cells. I can send a small sample of data if required. Regards sub Blank_Cells_with_Values() dim CELL range("A1").select set a=selection range(a,a.specialcells(xllastcell)).select for each CELL in selection if CELL.Text="" then CELL.value=cell.offset(0,3).value end if next end sub cell.offset(0,3).v...

How can I get the value of a field bit in Javascript
Hello, I am setting up a small script in Javascript which enables me to check if a field bit is with true or false and consequently to post a popup in Javascript (via the properties of the form in CRM). [Code] var champ_bloque = crmForm.all.new_bloque.Value; if (champ_bloque == True) { alert("bloqued"); } [/Code] This code does not seem to be good. However I checked in the base the valeure is well in True when I put yes in the field bit. Would somebody have an idea? Try this var champ_bloque = crmForm.all.new_bloque; if(champ_bloque.DataValue) { alert("bloqued");...

Rotating Stock Charts in Excel
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have read the threads on rotating Excel charts and realise that Stock charts cannot be rotated there. But I must rotate and put side by side 4 such charts (High-Low-Close). I cannot rotate them in Word or Powerpoint - the rotate function is disabled. I can rotate them and appose them very well in Appleworks but then I lose a lot of definition in the chart and the text. Any solution either in Microsoft or Appleworks <br> Thanks, <br> Kev Kev_Astan@officeformac.com wrote: > Version: 2008 Operating...

Can't Include more than one data range in chart
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel Whenever I try to add a second data range to a chart, I see the following message: <br><br>&quot;The Chart Data Range is too complex to be displayed. If a new Data Range is selected, it will replace all of the series in the Series Panel.&quot; <br><br>This happens with any data set on every type of chart; even a simple series of 10 numbers in a plain line graph. <br><br>Any ideas? ...

Setting DCB parameters in MFC app?
I have a VC6 SP5 MFC app that provides serial communications. I need to be able to set my DCB struct so that it is configured to communicate with a device that uses hardware flow control and also says that it needs RTS/CTS signalling. Do you know what values in the DCB I need to set in order to accomplish this? thanks, Jack Set the fRtsControl in DCB to RTS_CONTROL_ENABLE. You can use the WaitCommEvent function to wait for serial events (CTS is one of them) or you can call GetCommModemStatus to find out if CTS is on. "Microsoft" <jack@dxbase.com> wrote in message news:e...

plot schedule-type data in graph, date and times as x & y axes
want to plot schedule-type data on graph x axis is 30 days of month and 3 rooms per day; y axis is hour of day from 6am to 8pm in 15min increments, want to plot for say, the 3rd of the month in room 1: 7am-8:15, 10:30-11:15, 1145-1400, 1415-1530, 1600-1730, then other times for room 2, for each day of the month, several rooms per day. Is this impossible- if impossible in Excel, then is there another program that can do this??Thanx! weblun, What I came up with is this: Format column as time "13:30" Column B starting at cell B2 Enter "06:00" without the ""...

Find a cell(date) that corresponds to a particular maximum value
Hi there, I've got some temperature values on B5:I34 and I used the MAX functio to find the maximum temperature in that range. The maximum value is a cell F5. The date column is on cells A5:A34. My question is how can retrieve the date corresponding to that particular maximum value similar to minimum value? Appreciate any help. :confused: Pats -- Message posted from http://www.ExcelForum.com One way: Assuming *no ties* in the max and min temperatures in B5:I34 Using 2 empty cols, say cols J and K Put in J5: =MAX(B5:I5) Put in K5: =MIN(B5:I5) Select J5:K5 and copy down to K34 Put ...

stop entry of data that causes a negative value from a formula
Excel 2002. I tried to use "Validation" on the "Data" menu but that only works on entered data. I have a formula in a cell and if the answer becomes a negative number I want it to stop the user and "force" them to fix it. I used an IF condition but that only displays a message. Maybe you could fix the formula yourself: =max(0,yourformula) if the cell had to be non-negative. Or you could put the message in that same cell... =if(yourformula<0,"Please fix cells x, y, z",yourformula) That might be enough to make it so that they can't advanc...

Assign value to variable
Several people have helped me get to the stage where I've got the following formulae working: Range("B6").Formula = "=max(h1:h300)" Range("B7").Formula = "=(CELL("address",OFFSET(h1,MATCH(MAX(h1:h300),h1:h300,0)-1,0)))" Range("B8").Formula = "=ROW(OFFSET(A1,COUNTA(A:A)-1,0))" next question is for my 3 formulae, how do i set a variable to equal each of those values for writing to a sequential text file? TIA, Rob -- rroach ------------------------------------------------------------------------ rroach's Profile...

CE Fonts in MS Office X (Mac)
I am often using all the aplications within the MS Office X (latest version) in the Czech language (CE fonts). Everything works fine except when I type a smal "i" and a "space" key afterwards. The small "i" turns stubbornly into a capital "I" every time. Any cure for this small but annoying problem ? Please respond by e-mail : ovozeh1@mac.com Thanks ! OVozeh Hi The controls you are seeking are in Word. From the Tools menu choose AutoCorrect. You can control the setting for capitalization and correct things as you type. -Jim Gordon Mac MVP All respo...

4 field in legend of line chart
I have a Line chart in Access.i want to have 4 field in legend of chart.but access don't let to me to do it. While in design view of the chart, you can adjust the size of the legend. If this doesn't help, you need to provide more significant information about your chart, what you tried, and your results. -- Duane Hookom MS Access MVP "afshin" <afshin@discussions.microsoft.com> wrote in message news:30F4D785-2CCE-486B-87DC-309EE182D6E4@microsoft.com... > I have a Line chart in Access.i want to have 4 field in legend of > chart.but > acce...

Formula to make Negative Values Positive & Positive Values Negative?
I would like to know if there is any way to have a formula/function that takes a value from another cell (I know how to do that part) and then makes the number negative if the original is positive - or else makes the number positive if the original is negative. Any ideas? -- mustard ------------------------------------------------------------------------ mustard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20017 View this thread: http://www.excelforum.com/showthread.php?threadid=470855 Just multiply it by -1 example: =-1*A1 "mustard" wrote: ...

Setting Outlook 2000 AutoArchive longer than 60 days
Is there a way to do this? It is very tedious to do every folder at a time so I want to be able to set the AutoArchive setting to 365 days. Is there somewhere in the registry or is there a group policy that could be used to set this at the server? This is on Outlook 2000. Archive is an Outlook function, so there is no server setting for it. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "sprouty" <russell_fujak@hotmail.com> wrote in message news:1129891843.528907.125500@o13g2000cwo.googlegroups.com... > Is there a way to do ...

Retiring Asset with Non-Zero Net Book Value
Hi, I have recently started handling Fixed Assets and I have some doubts about the retiring assets in GP, When an asset is retired and has not been fully depreciated, the net book value is calculated as cost basis less LTD depreciation. Can anybody explain the underlying accounting concept for keeping the net book value. When an asset is retired, it means this asset has no value for the company. Isn=92t it so ? Is there any way in GP to make net value zero when it is retired ? Thanks. ...

Display line chart in customize reports
How do I display a line chart in viewing charts? The line and Pie options are greyed out. Thanks Wayne wrote in <89D9F87A-E7F0-4EDC-883B-EE4AB1818B18@microsoft.com>: >How do I display a line chart in viewing charts? The line and Pie >options are greyed out. If the option's greyed out, you're out of options. :-) Workarounds: * Look at the other reports. Maybe one of them offers what you want. * Export the data to a spreadsheet. You can do anything you want there. If you'll tell us what you're trying to accomplish, maybe someone can offer you some alternati...