Dynamic Chart Help!

I want to create a line chart showing monthly data from April 2001 up t
and including the current month. (ie, my data will be expanding eac
month.)

I currently receive the data in a spreadsheet and any month that ha
not yet occurred contains a zero.

I keep reading about Dynamic charts, but I have no idea what they ar
and have never 'named' a range before, so can you given me a dummie
guide to doing this please

--
Message posted from http://www.ExcelForum.com

0
7/30/2004 9:18:27 AM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
562 Views

Similar Articles

[PageSpeed] 31

Christine -

Making a dynamic chart is not too complicated, but you can't just read 
about it, you have to work through a couple examples. I have a couple 
examples here, and links to more examples as well:

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

You need a few formulas to do this. If you know where your data starts, 
the formulas are easier. Defining a name merely means applying a name to 
a range of cells, and you can use a formula for this.

If you know the first cell with data, the formula is easier. Say the 
monthly data starts in E5, and extends down column E. Press CTRL+F3 to 
open the Define Names dialog (or go to Insert menu > Names > Define). 
Enter a name for the first cell, MyFirst, click in the Refers To box, 
then select E5 with the mouse. The Refers To box now says =Sheet1!$E$5. 
Click Add, and you've created your first named range.

If you know it's column E but aren't going to know the first cell, this 
formula defines MyLast as the first numeric cell in the column:

=INDEX(Sheet1!$E$1:$E$65535,MIN(IF(ISNUMBER(Sheet1!$E$1:$E$65535),ROW(Sheet1!$E$1:$E$65535),65536)),1)

It means (look up INDEX in the help files) find within the range 
Sheet1!$E$1:$E$65535, the cell in the row defined by 
MIN(IF(ISNUMBER(Sheet1!$E$1:$E$65535),ROW(Sheet1!$E$1:$E$65535),65536)) 
and in the column 1. That bit inside the min returns an array (list) of 
numbers, which is equal to the row of the cell if it contains a number, 
or is equal to 65536 if it doesn't contain a number. (so above the table 
there can't be any numbers in column E). In the example I gave, where 
the data starts in cell E5, this array is {65536, 65536, 65536, 65536, 
5,6,7,8, etc}. The first four cells are non-numeric, so the first four 
numbers are 65536. There are numbers in E5, E6, etc, so the row numbers 
are 5,6, etc. The min of the list is 5. Index(E1:E65535,5,1) then means E5.

Now you need a formula for the last cell. Enter MyLast in the Name box, 
and the refers to formula is

=INDEX(Sheet1!$E$1:$E$65535,MAX((Sheet1!$E$1:$E$65535>0)*ROW(Sheet1!$E$1:$E$65535)),1)

This finds the last cell with a positive value in column E.

Let's combine MyFirst and MyLast into a formula for the entire range of 
cells they span. In the name box, enter the name MyValues, and in the 
Refers To box, enter a much simpler formula:

=MyFirst:MyLast

Of course, you could combine the two previous formulas into a single 
monster formula for MyValues:

=INDEX(Sheet1!$E$1:$E$65535,MIN(IF(ISNUMBER(Sheet1!$E$1:$E$65535),ROW(Sheet1!$E$1:$E$65535),65536)),1):INDEX(Sheet1!$E$1:$E$65535,MAX((Sheet1!$E$1:$E$65535>0)*ROW(Sheet1!$E$1:$E$65535)),1)

That's your defined name for the Values. To define a name for the 
months, enter the name MyMonths, then enter a simple formula:

=OFFSET(MyValues,0,-1)

This means define a range the same size as MyValues, located down 0 rows 
and right -1 rows (or left 1 row). I assume the months are in column D 
next to the values.

Now to make the chart. Click the chart wizard button, or find Chart on 
the Insert menu. In step 1, select the chart type. In step 2, click on 
the series tab. In the Name box, type the name of the series (the legend 
entry for the series), or click in it and select the cell with the 
mouse. Clear the Y Values box, and enter =Sheet1!MyValues, referring to 
the monster defined name from above. In the X Values (Category Labels) 
box, enter =Sheet1!MyMonths. Continue to the end, and you will have your 
first dynamic chart.

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


Christine Wilso < wrote:
> I want to create a line chart showing monthly data from April 2001 up to
> and including the current month. (ie, my data will be expanding each
> month.)
> 
> I currently receive the data in a spreadsheet and any month that has
> not yet occurred contains a zero.
> 
> I keep reading about Dynamic charts, but I have no idea what they are
> and have never 'named' a range before, so can you given me a dummies
> guide to doing this please!
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 

0
7/30/2004 11:26:00 AM
good question...

the way i solved this was to name a range (using the 
insert --> name -->  define range dropdown menu command), 
and then create a chart that points to the named range in 
its data source fields.

Next I used the following macro to expand the named range:

With Range("ndx1")
        .Resize(.Rows.Count + 1, 1).Name = "ndx1"
        End With

Here my named column range is called ndx1.  Each time I 
run the macro it expands the column of data by one row 
down - and presto! the chart is dynamically updated.

>-----Original Message-----
>I want to create a line chart showing monthly data from 
April 2001 up to
>and including the current month. (ie, my data will be 
expanding each
>month.)
>
>I currently receive the data in a spreadsheet and any 
month that has
>not yet occurred contains a zero.
>
>I keep reading about Dynamic charts, but I have no idea 
what they are
>and have never 'named' a range before, so can you given 
me a dummies
>guide to doing this please!
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
anonymous (74723)
7/30/2004 1:44:00 PM
Reply:

Similar Artilces:

Macro help #8
Here is what I have: Sheet3(pcdata) data is in rows A3:R19 I need to copy and paste(Macro) this data to Sheet4(Total) The first available row is A3 Sheet4(Total) The Sheet3(pcdata) will be updated daily the macro needs to paste the data on Sheet4(Total) below the existing data,, (next available row). As I am quite a novice at excel. any help.. is very appreciated. Thanks Mike Hi Mike Try this http://www.rondebruin.nl/copy1.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Mike" <mbrune@insightbb.com> wrote in message news:rBKle.11597$Is4.5585@attbi_s21... &g...

Value Limits In A chart
Is there any way possible to change the color of a data series within a chart when it exceeds a value of another data series? Thanks!! Hi see: http://peltiertech.com/Excel/Charts/format.html#CondChart and http://peltiertech.com/Excel/Charts/format.html#CondChart2 -- Regards Frank Kabel Frankfurt, Germany "Robby" <Robby@discussions.microsoft.com> schrieb im Newsbeitrag news:03E95BC9-B246-41E9-A941-177C2C22CB4F@microsoft.com... > Is there any way possible to change the color of a data series within a chart > when it exceeds a value of another data series? > > Tha...

Data markers in a stacked chart will not sit side by side by month #2
How can I get the monthly columns (ie: July Actual & July Budget) in a stacked chart to sit side by side with a space before the next month (August Actual & August Budget) and so on for each month. ...

Help on formula
Greetings! I have the following columns: A. Cost B. Plan C. Additional D. Total $100.00 OTN $35 $135 OTN2 $40 $0 $150.00 OTN4 $25 $175 How can i get the TOTAL field to calculate Column A and C only when there is a value entered in Column A? In case you are wondering, the Additional Column (c) has assigned values, however I don't want a...

Anybody Help with previous question
Hi, I'm getting desperate to solve this, so my apologies for posting this again, but can anybody offer any help with this previous question. If the link doesn't work I have copied the orig question below. http://www.microsoft.com/office/community/en-us/default.mspx?pg=4&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.misc&fltr=&mid=4044fba5-9f57-4be7-85a7-92ebb9093772 I have (been given) the code below that will add a row of 'jobs' to a worksheet named 'Database'. There isa check to see if these jobs have been already added a...

Pivot Chart in Excel 97
Hi guys, i was wondering if anyone knows a way to provide a dynamic chart based on a pivot table with similar functionality to the pivot chart in Excel 2000 or newer? Cheers The Frog *** BUMP *** 24hrs Bump has no meaning in newsgroups. Mr.Frog.to.you@googlemail.com wrote: > *** BUMP *** 24hrs It got your attention Bob :-) Mr.Frog.to.you@googlemail.com wrote: > It got your attention Bob :-) Yes, but what was your orignal question? If you had quoted it in your "bump" you had at least a chance of an answer. But this is a newsgroup and folks don't generally kee...

Getting rid of "What's this?" help on a property sheet.
Hello, Does anyone know how to get rid of the "What's this?" help on a property sheet? That is, whenever I right click a button I get a pop up menu that says "What's this?" and we want to get rid of it. I've tried just about everything, but the simple answer escapes me (if there is one). Thanks for any help. ----------------------------------------- Barry H barry at terramartech.com i think that is the 'context help' which is the DS_CONTEXTHELP style. you should be able to turn it off on the 'more styles' tab of the properties for your...

Security help!
I am going to use webservices extensively in my vb windows forms application. I will be exchanging lots of data over the internet in my distributed app. I purchased a certificate from verisign to encrypt it via https. My questions are these: 1. At what point does the https protocol encrypt the data? Does it encrypt inside the CLR, or just before it hits the ip subsystem, or does it send the webservice request THEN encrypt it? 2. Each of my webservice function calls receives a userid and password in addition to any data it is carrying to ensure that the user has permissions to that f...

service calendar in MS Dynamics 4.0
Does anyone knows how we can achieve the following requirement with MSCRM 4.0 In the service calendar in MS Dynamics 4.0 you are able to create new appointments and service activities. Appointments and Service Activities which are created will be present in the Service Calendar. To see all details of such a record[Subject(default Value)] you must open it. But by moving your mouse on the concerning appointment or service activity, a text-box-view appears with primary information. With appointments the content of the following fields is displayed: 'show time as', 'subject...

Chart tabs changing by themself
I have an Excel 2007 spreadheet (.xlsx) file with about 40 charts all in their own sheets. Every one of those sheets used to have their own descriptive tab name. Excel does not seem to like that, however, as sometimes after closing, it just deletes all of my tab names replacing them with a generic Chart3, Chart4, Chart 5 etc. This is highly frustrating of course. I once edited every one of those tab names back to their original names, wasting a lot of my time in the process, hoping it would not happen again. Alas, it sporadically happens again and again... Any ideas/solu...

dynamic resource
Hi, Anybody know how to load e.g. bitmap into resource dynamicaly? The bitmap file will be downloaded by application itself , so it has to be somehow inserted into resource and displayed. thanks What do you mean by "into resoure dynamically"? Do you want to load a ..bmp file and display it? If yes, take a look at GDI+, which is by far the easiest way to achieve this. ---------- Ajay Kalra ajaykalra@yahoo.com I misspelled this, yes I'd like to load new bitmap on the fly. But resource file looks like is static. I use ATL/MFC applicatation. "Ajay Kalra" <aja...

Adding Comments From Cells To Chart Points.
Hi all I have spent a large portion of my weekend trying to find the answer to this. Without much luck! I have finally come up with my own code and was hoping someone could have a look at it. I'm looking to clean it up and make it a little more efficient. Thanks in advance, Greg. Sub AddCommentsToChartPoints() Dim ws As Worksheet Dim ct As ChartObject Dim serSeries As SeriesCollection Dim ser As Series Dim Counter As Integer Dim ChartName As String Dim xVals As String Dim xAddress As String 'Loop through each worksheet in workbook For Each ws In Worksheets 'Lo...

Combining multiple Stacked Charts
I have 3 scenarios with info on Asset A and Asset B for 10 years. I can create stack charts for each scenario independently, but can not figure out how to combine all 3 onto 1 chart across multiple years. x axis is year and each year should have 3 stacked lines Y Axis should be total value of both Assets Any help appreciated Does this help? http://peltiertech.com/WordPress/2008/05/19/clustered-stacked-column-charts/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "JANeyer" <...

Dynamic sum in Chart
Hello: So I've got this pivot chart that I've created, and it's great, EXCEPT when I select different categories to show up, I want to show a SUM of the categories, not the values of each category. I would appreciate any help/advice you may have. Thank you! Michelle In the pivot table you can select different thing like count and SUM etc. If you right click in the upper left vacant square and chose field settings, then select what you want to see in your data section. Misha wrote: > Hello: > > So I've got this pivot chart that I've created, and it's great...

"x86 and Any CPU" Platform configuration...Help please.
Hi all, I am trying to understand the difference between Any CPU platform configuration and the x86 platform configuration. The thing is ... I initially had a C# project compiled as "Any CPU" on VS 2005 professional edition and then when it was run on a 64bit machine it gave a system.badimageFormat exception. Upon researching it , i found tht we need to change the project platform configuration to "x86" ..... this worked. So my Q is whts the basic difference between x86 and Any CPU...are there any disadvantages of using either of them? And in a paricular soltuion contai...

SharePoint Alternate Access Mapping to my Dynamics GP BP site
Hello, If I have posted this question in the wrong forum please let me know! I’m looking for some guidance on changing the Alternate Access Mapping for my Dynamics Business Portal site. So I can implement Single Sign On via ISA Server which is supported on one web listener. Present configuration: Default Zone: http://geneva:222 I would like to add: Internet Zone: http://dynamicsbp.proclub.com I presently have another SharePoint Collection (The Main Portal) http://geneva configured which listens on Port: 80. I tried to add the Alternate Access Mapping http://dynamicsbp.proclub.com...

Using Formulas with Charts
I have a chart that references cells with the following formula =if(a1="","",a1/a2). The formula is used for each month Jan-Dec. I would like to copy the formula across all months. When I do this my data point show as zero for months that have not happened yet. What can I do to not show a line on the chart for a data point with a "" or null value? Thank you, Sandy The cell with the formula isn't blank, and "" does not produce a blank. There is not a formulaic way to simulate a blank, but for many cases, a line chart or XY chart will treat...

Outlook 2003 opens to Internet Properties
When I shut down last night, all was well. This morning when I went to my Start button and Hit Outlook - it opened to the Internet Properties General Page - I finally got it to start bu going to run and typed outlook and all was well. Just shut down and restarted and it happened again! What can I do to get it to open properly. I have Outlook 2003 and Windows XP. Thanks so much! Right-click on the Outlook 2003 icon (the icon that opens Internet Properties) and choose "Properties". Post back what you see in the "Target" box. Tim "Terri" <terri@mylanusa.com...

McAfee SystemGuard shuts down Publisher 2000! HELP!!!
OPSys: Windows XP Publisher version 2002 A few months ago McAfee was updated on my computer. When I tried to open my Publisher (v. 2002) files McAfee SystemsGuard would shut it down or lock up my computer. I temporarily loaded an old version of Publisher on my computer, however, I could not open my old files nor did it offer the same abilities to do certain functions so I could not duplicate some of my documents. Yesterday, I reinstalled version 2002. The same thing started happening. While I was once again able to open my previous files, it would immediately lock up and try to s...

Area Charts
I have four lines that each define a horizontal zone. When I change each to an Area Chart, it appears that the color is only painted above each line and the color extends from the line all the way to the top of the chart. But above the top line I'd like this to be uncolored. I want the top two areas are filled in BELOW each line to a horiz. mid-point on the chart and the bottom two areas are filled in ABOVE these lines to a horiz. mid-point. In effect I want the chart to depict from top to bottom: Empty space Danger zone Safe zone Safe zone Danger zone Empty space Help!! Cr...

Tab seq dynamically...
I have a button which added dynamically to a property sheet. Now for setting the tab sequence with this new button, Am using SetWindowPos() method. Now tab sequence is proper. THE PROBLEM IS Through the arrow keys, I could not navigate to the last button for which I called SetWindowPos. where as through tab keys I could go to the last control. I will appreciate ur help in this. DETAILED DESCRIPTION I have three buttons (OK,Cancel, Help)on the property sheet. I added a button "Default" to the property sheet and positioned between Cancel and Help. I set the tab sequence as ...

Page header and footer for charts from my template
I plot a lot of Y vs X data as scatter plots. Usually, I want the graphs as separate sheets. For these sheets, I want a standardized header and footer. Presently, I do this manually by 1. Select the chart location as New sheet, with a name 2. In the new sheet, click the Print Preview button 3. Click Setup... 4. enter Custom Header and Custom Footer data. I always enter the same data in step 4 above. It should be ideal for a template. Is there a way to use a template for this? Next time you add charts, assuming you add them to end of your workbook, click the last sheet that already...

no transport provider Help!!
We are running Exchange 5.5 and Outlook 2000. We recently have not been able to send e-mail to anyone. We found out that Qwest is blocking Port 25. Does anyone know how to resolve this issue? Thank you for your help in advance! Candace Sparks Configure your Exchange 5.5 server to send ALL outgoing email to your ISP's SMTP server. "News" <rsparks24@comcast.net> wrote in message news:erq6d.171911$3l3.101391@attbi_s03... > We are running Exchange 5.5 and Outlook 2000. We recently have not been > able to send e-mail to anyone. We found out that Qwest is b...

dynamic charting #2
Hi I have to update a weekly spreadsheet With week numbers in column B x series and three y series percentages in G,H,I columns. Could someone please help I need these to update automatically. If someone can explain in detail I would appreciate 39 2097 2165 68 1770.7 97 96.86 118.43 40 2443.15 2201 -242.15 1548.93 97 111.00 157.73 41 2162.83 2259 96.17 1600.78 97 95.74 135.11 42 2163.08 2241 77.92 1545.1 97 96.52 140.00 43 2137.5 2271 133.5 1664.6 97 94.12 128.41 44 2160.67 2298 137.33 1455 97 94.02 148.50 Ta Brian ...

Combine Bar with Single Line Chart
Hello, Please can someone help me with this question. I am trying to create a bar chart with a single line chart. The bars will display the totals for various years i.e. 2004 104 2005 107 2006 108 2007 110 2008 101 The line chart will show how 2008 is performing on a weekly basis i.e. 01/01/2008 = 100 07/01/2008 = 101 14/01/2008 = 102 The bar chart for 2008 will reflect this also, updating each time a new week is added. The Y Axis I will assume display a total i.e. 99-111 for example. The X Axis needs to show the dates plotted for the line chart 01/01/2008 - 31/12/2008 (weekly) but how...