Changing graph axes using named range

Hi,
I have a large table of data, each column is defined as a range. What
I would like to do is use the drop down list (using data validation)
where each option in the list is a named range, to change the graph's
x and y axes.
For example, if I were to select the named range for column C as my x
axis (using the drop down list) and column F as my Y axis, I would
like the graph to automatically plot this without going into the
=SERIES(<legend entry>,<X Values>,<Y Values>,<PlotOrder>)
formula and changing the named range manually. I have tried to
reference the cells in this formula where I have written the name of
the range's in them but it doesn't work. Can anybody help?
Thanks a lot!
Simon

0
4/5/2007 1:36:50 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
504 Views

Similar Articles

[PageSpeed] 24

Here is a start.I have:
1) text "X","Y","Z" in A1:C1                (no quotes,of course,anywhere)
2) In G1 the text "Y"
3) In D1 the formula =G1                   (it shows "Y")
4) In A:C2 the numbers for x-values, y-values, z-values
5) I have named B2:B10 as Y and C2:C10 as Z
6) In D2 the formula =INDIRECT($G$2) and this is copied down the column
7) A chart with x-axis A2:A10 and y-axis D2:D10
The chart plots the Y values
When I type "Z" in G1 the chart plots the Z values
best wishes
-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

<simon.stewart@uk.fid-intl.com> wrote in message 
news:1175780210.819114.164550@y80g2000hsf.googlegroups.com...
> Hi,
> I have a large table of data, each column is defined as a range. What
> I would like to do is use the drop down list (using data validation)
> where each option in the list is a named range, to change the graph's
> x and y axes.
> For example, if I were to select the named range for column C as my x
> axis (using the drop down list) and column F as my Y axis, I would
> like the graph to automatically plot this without going into the
> =SERIES(<legend entry>,<X Values>,<Y Values>,<PlotOrder>)
> formula and changing the named range manually. I have tried to
> reference the cells in this formula where I have written the name of
> the range's in them but it doesn't work. Can anybody help?
> Thanks a lot!
> Simon
> 


0
bliengme5824 (3040)
4/5/2007 1:50:52 PM
With a few adjustments that seems to do the trick thanks!
Is there a way of making the named ranges more dynamic? I know of the
counta formula so it goes to the end of the data when you add more,
the only problem for me being that my columns are filled with if
formulas that are taken into account using the counta formula. Is
there an alternative?
Thanks,
Simon

0
4/5/2007 2:51:04 PM
Reply:

Similar Artilces:

Changing Exch5.5 GAL columns in Outlook client view
Sorry if the subject line is a bit cryptic; anyway, in the Outlook client, if you display the 'Address Book' you get the following columns - name, business phone, office, title, company, alias, e-mail type, & e-mail address. How do you change it so you show the display name, business, extension, cell phone, & internet email address? Basically, how/where do you change the columnar data the client sees? Thanks, Mike Lawson Mike Goto View | Columns and there u can manage views... KJ "Mike Lawson" wrote: > Sorry if the subject line is a bit cryptic; anyway, i...

how to change the focus of edit box?
hi! how to change the focus of edit box while i m editing it....? I have started SDI. In it, i hav created a window having same size as the view...class, now adding a edit box in it... while editing the edit box if someone press the enter key the focus shd go to ... the current window from currently editing window.. i know i must have to modify class CEdit n its OnChar func... n i hav to check for VK_RETURN. but my problem is quite different. i have been created class CChildWindow : CWnd and class CEditLoseOnEnter: CEdit now i want to use the object of CChildWindow to give focus whenever t...

problem using CxImage
I'm trying to use the CxImage class (you can find it on CodeProject.com). Attempting to follow the example in the docs for using CxImage to draw to a picture, I've added the include, the dependencies, the pointer to the .lib in the LINK tab of the settings. My code says CxImage image("pic.jpg", CXIMAGE_FORMAT_JPG); This compiles, but generates a link error - ImageCoordsDlg.obj : error LNK2001: unresolved external symbol "public: __thiscall CxImage::CxImage(char const *,unsigned long)" (??0CxImage@@QAE@PBDK@Z) I can call almost any other CxImage method, ...

Changing Averages
If I want to average the numbers in these two columns that's easy. But what if every day I add a new number to the bottom of each column, and I only want to average the latest ten numbers? In other words, the oldest number (on top) drops off of the average. How would I do that? 85 86 86 85 105 92 90 100 90 86 106 83 97 85 87 101 92 89 90 93 94 97 =AVERAGE(OFFSET(a11,-10,0,10)) change a11 to the last range of data "JimS" <jimx22@msn.com> wrote in message news:u0mj659u8i36ellr3bcj9l9eh6gsa74f66@4ax.com... > If I want to average the numbers in ...

Abnormal program termination while using "eseutil /d"
Hello, I tried to defrag our public folder server (Exchange 5.5, SP4; WINNT 4.0 SP6a) because of a big database (near 185 GB, available space on Disk = 203 GB) Because of the size I used a Remoteserver to store the temp defrag Database (Free Space on the remote Disk are 273GB). To run the defragmention I used the following command: --> eseutil /d /ispub /tg:\REMOTESERVER\Pub_defr.edb /p At about 90% of defragmentation (166GB) "eseutil" terminated and wrote the following information: Microsoft(R) Windows NT(TM) Server Database Utilities Version 5.5 Copyright (C) Microsoft Cor...

can't see new e-mail in inbox.Using Outlook 2003
I have been using Outlook now for about a month as my new e-mail client instead of Outlook express. Everything has been working fine until last nite when all of a sudden, when I was checking my e-mail, I was unable to to open my e-mail. I have it set to open up in my inbox with the preview pane, but none of the messages appear. I am using the filter to seperate the spam, & junk e-mail. Those folders open up fine when I click on them as well as the messages being displayed. So why won't the messages be displayed in the Inbox? I have turned off my firewall to no avail. I have a...

Changing Business Unit Organisation & Avoiding Reassigning Roles
Hi all, I was wondering if anyone had a tip for the following problem encountered in CRM 3.0. Our Business Unit organisation changes from time to time (to stay in line with the Sales organigramme). Each time we move a child Business Unit from one parent Business Unit to another, the user roles are lost. Is there any way around this? I suspect that you're going to say no, but I figured I'd ask before the changes we've got to do this week on the off chance I'd be wrong... Many thanks in advance, Christopher -- Christopher Grove Christopher, this is by design so there is n...

VLOOKUP Function using multiple worksheets
Received advise previously on this (thanks). However, I need to look up entries in another separate worksheet and it won't work. I'm using: =VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New Clients'!A2:O20,2,FALSE). It returns the persons name (great) but I need to transfer across other details too. Basically I'm summarising one worksheet onto a specified format in another. In addition, I somehow want it to only show me those clients in 'Entry 1 - New Clients' if the corresponding column (N) has the entry of "Active". Is this possible??...

SMTP Connector not being used and has odd name in queue
I have a 4 node Exchange 2003 A/P cluster w/ a non clustered server as the front end. Right now, all of the servers use their own SMTP VS to send email. I want them all to use the front end as a bridgehead for outbound email. Easy enough... So, I create the SMTP connector and list the front end as the bridgehead, and add in the address space, as I've done many a time. All appears good. But. As soon as I do this, all outbound mail stops. I then look at each server and see that they show the SMTP connector and it's filling up. Also, the name of the connector isn't what I called it...

what is the attribute name for this : "automatically update e-mail addresses based on email-address policy"
There is a check box above for the exchange 2007 console. What is the equivalent of the attribute name ? "EmailAddressPolicyEnabled" -- Tim. "IT STAFF" <jkklim@hotmail.com> wrote in message news:4E9A4E62-2028-435A-B982-0059BAA273F8@microsoft.com... > There is a check box above for the exchange 2007 console. > > What is the equivalent of the attribute name ? ...

Can a 'ref' parameter be saved for later use?
Hello out there, I am passing a string[] to a form's constructor, it gets displayed in a ListBox, the user can change it and in the end I want it back. I have a solution I don't particularly like and thought about using a 'ref' parameter. Inside a function I can use it to 'pass back' data - but in this scenario the 'passing back' will have to happen later. So I thought I could declare a (normal) variable to be a 'ref' - but the compiler doesn't like it. // class variables ref string[] strLstFromCaller; // compiler error Form(ref st...

Sum Range
Hi, is there a way to limit a running sum to the previous 12 or so records? For example, in Excel I can use =sum(a1:a12) in cell b12. Then copy down that formula into cells b13 thru b24 automatically changing the 1 and 12 to 2 and 13, 3 and 14, 4 and 15 and so on always summing the last 12 values. Thanks, Mike No and Yes. Access stores record in a big bucket. When you view a set of records, the record number is for that display but is not related to the record itself. With a different sort or criteria the records will indicate a different record number. You must have something...

Automatic document file name & path in all new document footers
Will an Office template or other function enable automatic footers on all new documents that includes the file name and path? How? Yes, if you add the Filename \p field to the template footer. It's not advisable to do this in the Normal template, however. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Dora the Explorer" <Dora the Explorer@discussions.microsoft.com> wrote in message news:F6569F09-768E-4F1C-9C31-366EB9290794@microsoft.com... > Will an Office template or other function enable aut...

Character Count Range of Cells
How can I get a character count for text in a large range of cells in a worksheet? I can only get LEN to count a single cell without a #VALUE! error. -- michimac You could use {=SUM(LEN(A1:A20))} Note that the curly brackets indicate an Array formula DO NOT type them simply type =SUM(LEN(A1:A20)) and the prest Ctrl+Shift+Return the formula will be entered and the brackets as well. Change the range to suit -- _______________________ Naz, London "michimac" wrote: > How can I get a character count for text in a large range of cells in a > worksheet? I can on...

How do I change the default email Template folder in Outlook? (nt)
Why do you think that's something you want to do?=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "corn" <corn@discussions.microsoft.com> wrote in message = news:B6BFA326-22FD-467B-B2E6-49E076D388F5@microsoft.com... > I like to save these for easy access, so I can attach them to email without thumbing through several folders. On to...

Change data source of existing report
I have an existing report and would like to maintain the current format but use a different query as the source data. Is it possible to do this? I realize that I will have to change the fields but I like the format and do not want to have to create a new report if possible. Caldo wrote: >I have an existing report and would like to maintain the current format but >use a different query as the source data. Is it possible to do this? I >realize that I will have to change the fields but I like the format and do >not want to have to create a new report if possible. ...

Mail merge using Excel data
Using P2003 and mail merge In Pub, whenever my "zipcode" field (that I am getting from an Excel file) has zip +4 digits, "0" shows up in the Pub merge results. In the Excel file, I have tried having the column attributes set as Custom, Text, and others to see if that was causing the problem, but still the same results. How can I get the zip + 4 to display correctly in the Pub merge? Thanks much for your help. Try inserting an apostrophe before the figures in Excel. This tells Excel that the cell contains text. Does this work for you? -- Rae Drysdale "ladyt...

Graph paper for charting patterns/showing objects to scale in a sp
Trying to locate the correct Office product to be able to layout a physical space showing objects to scale. AND being able to use graph paper to show a pattern Visio - but what you really want is some sort of a CAD program. By the way, this newsgroup is for people with questions about Publisher specifically, not Office in general. -- JoAnn Paules MVP Microsoft [Publisher] "Elaine" <Elaine@discussions.microsoft.com> wrote in message news:E0FDBF22-79EC-466E-AF54-56586A7BF503@microsoft.com... > Trying to locate the correct Office product to be able to layout a >...

Using a Web Query
Hello Everybody I have been trying to retrieve information from the Web by using a Web query, and then inserting the information into an active worksheet in Excel 2000. In the "Returning External Data to Microsoft Excel" dialog box I want to use the Parameters button, which is not active, Could anybody tell me how to activate the Parameters button before using the "Get the value from the following cell" option button? Thank you in advance. Hi. It is possible to supply parameters to a webquery. You can do it programmatically as described in this KB article: http:/...

Asian Language change in Excel tabs and worksheets
A file that I created has been changed somehow. It now has both US English and what looks like some sort of Asian. It has also changed what is shown in the font type to some strange symbol type. 楲污††† Any ideas how this could happen, and how I can fix my file. It has infected both sheet data and tab names. Ryan Couple of things to look at. Tools>Options>General>Standard Font Format>Style>Normal Other than those two??? Gord Dibben Excel MVP On Mon, 10 Jan 2005 11:41:03 -0800, "Ryan" <Ryan@discussions.microsoft.com> wrote: >A file that I created has ...

Using an A03 mdb in A07
I have an Access 2003 database that I need to be able to run on a machine that's running 2007. Theoretically, it should work in both versions of Access. However, an associate of mine has reported that he had problems running his A03 mdb in A07. Apprently, whenever he opened a form using the acDialog property, the form opened as a single horizontal line (a "slither," as he called it). He then resized the from in A07 and saved it in A07, and then it opened fine with the acDialog property. Has anyone else come across this behavior of A07? Are there any other issues when usi...

Using different drivers by USB REV
We have an existing product that has our standard USB VID/PID. We haven't set the USB REV to anything so it defaults to FFFF, but the .INF file for our driver doesn't specify the REV so it works fine: USB\VID_1234&PID_1234&MI_00 We now have to introduce a new version of the product that uses the same VID/PID, so we thought we could use the REV to make sure it uses the new driver. The new product sets its REV to 0008 (no particular reason it has to be this) so the .INF file looks like: USB\VID_1234&PID_1234&REV_0008&MI_00 This seems to work fine - the...

How can I change primary display for presentation on multiple mon.
I want to hit F5 and have my presentation go to a different monitor. I have multiple monitors so my laptop display on a classroom screen. Can I change the default monitor that F5 points to? In article <5A95BBFC-B291-4A05-9E71-3F094B937ABB@microsoft.com>, Shorebilly wrote: > I want to hit F5 and have my presentation go to a different monitor. I have > multiple monitors so my laptop display on a classroom screen. Can I change > the default monitor that F5 points to? In the Slide Show | Set up show dialog, you can choose the monitor you want to display slide s...

How to change the color of the series markers from yellow to Black
Hello I have a chart with 3 series on it. Every point of one series is represented by a blue square. Every point of another series is a yellow triangle. Every point of the third series is a red circle. Is it possible to change all of the colors to black? Thank you! Sorry, I have just figured this out. "Sam" wrote: > Hello > > I have a chart with 3 series on it. Every point of one series is > represented by a blue square. Every point of another series is a yellow > triangle. Every point of the third series is a red circle. Is it possible > to chan...

Different version error using vista
I am getting the following error: "A prodauct on your computer is on a different version than the database version. You will not be able to use the application until this issue is resolved. Use the GP_LoginErrors.log file in your temp directory to assist in resolving this issue." I tried to look for this log on the local but am unable to find it. I spoke to a rep from Dynamics and the said it is compatible with VISTA. it installed great but now I am getting this error. I had the same thing. You should be able to find the log file, however. Another approach is to go to a wor...