Getting charts to automatically pick data from a series

Can I get a chart to always use say the last 9 values of a series?

Does excel also do interactive charts?


0
Salmonh20 (2)
4/6/2006 8:04:02 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
860 Views

Similar Articles

[PageSpeed] 6

Salmonh:

Yes you can have Excel chart the last 9, 30 or whatever number of values 
that you would like.

You'll need to use dynamic range names to do this. Here are several links 
that will get you started.


      http://www.stfx.ca/people/bliengme/ExcelTips/Dynamic.htm
      http://processtrends.com/pg_chart_tips.htm#Dynamic_Chart_Range_Names
            http://peltiertech.com/Excel/Charts/Dynamics.html


The idea is to create dynamic ranges that will update as you add data and 
will set the last 9 values as your plot range.

For a simple data set of X and Y in Cols A and B, you would use 4 offset 
formulas:

First we set up a range name for the last data row

         last_row =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0)

Second: we set up a range name for the first data row that we want to plot, 
using your 9 example:

          first_row
      =OFFSET(last_row,-8,0)


Third: we set up the dynamic X value range name using the first and last 
rows:

           dyn_x =OFFSET(first_row,0,0,last_row-first_row+1)


Fourth: we set up the dynamic Y value range as an offset to the dynamic_X 
range

           dyn_y =OFFSET(dyn_x,0,1)


We are now set to create our chart. I do this by creating my chart using the 
chart wizard. When I have my chart  formatted and set the way I want, I edit 
the chart series formulas and replace the X value range with dyn_X and the Y 
value range with the dyn_Y range name.

If you want another period besides 9, edit the offset formulas.


You also asked about interactive charts. There are many interactive 
examples. If you give me an idea of the type of interaction you are looking 
for, I can give you links to specific examples.

...Kelly

koday@processtrends.com




"Salmonh20" <Salmonh20@discussions.microsoft.com> wrote in message 
news:7C6C7AA7-19F3-4096-8BD0-EF3C974A1B87@microsoft.com...
> Can I get a chart to always use say the last 9 values of a series?
>
> Does excel also do interactive charts?
>
> 


0
dkod (205)
4/6/2006 9:48:07 PM
This one shows the last 12 values, which is easily adjusted to 9:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions - 
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______

"Salmonh20" <Salmonh20@discussions.microsoft.com> wrote in message 
news:7C6C7AA7-19F3-4096-8BD0-EF3C974A1B87@microsoft.com...
> Can I get a chart to always use say the last 9 values of a series?
>
> Does excel also do interactive charts?
>
> 


0
jonxlmvpNO (4558)
4/7/2006 12:04:36 AM
Kelly,

Thanks that was really helful.
Re: Interactive charts, on a set dat I have 20 different lab results on a 
sample, I have 500 sample per year.
I would like to send the results as an interactive chart which will allow 
the user to pick from the 20 different result types, what they wish to view, 
changing from one to another.

Robert 

"Kelly O'Day" wrote:

> Salmonh:
> 
> Yes you can have Excel chart the last 9, 30 or whatever number of values 
> that you would like.
> 
> You'll need to use dynamic range names to do this. Here are several links 
> that will get you started.
> 
> 
>       http://www.stfx.ca/people/bliengme/ExcelTips/Dynamic.htm
>       http://processtrends.com/pg_chart_tips.htm#Dynamic_Chart_Range_Names
>             http://peltiertech.com/Excel/Charts/Dynamics.html
> 
> 
> The idea is to create dynamic ranges that will update as you add data and 
> will set the last 9 values as your plot range.
> 
> For a simple data set of X and Y in Cols A and B, you would use 4 offset 
> formulas:
> 
> First we set up a range name for the last data row
> 
>          last_row =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0)
> 
> Second: we set up a range name for the first data row that we want to plot, 
> using your 9 example:
> 
>           first_row
>       =OFFSET(last_row,-8,0)
> 
> 
> Third: we set up the dynamic X value range name using the first and last 
> rows:
> 
>            dyn_x =OFFSET(first_row,0,0,last_row-first_row+1)
> 
> 
> Fourth: we set up the dynamic Y value range as an offset to the dynamic_X 
> range
> 
>            dyn_y =OFFSET(dyn_x,0,1)
> 
> 
> We are now set to create our chart. I do this by creating my chart using the 
> chart wizard. When I have my chart  formatted and set the way I want, I edit 
> the chart series formulas and replace the X value range with dyn_X and the Y 
> value range with the dyn_Y range name.
> 
> If you want another period besides 9, edit the offset formulas.
> 
> 
> You also asked about interactive charts. There are many interactive 
> examples. If you give me an idea of the type of interaction you are looking 
> for, I can give you links to specific examples.
> 
> ...Kelly
> 
> koday@processtrends.com
> 
> 
> 
> 
> "Salmonh20" <Salmonh20@discussions.microsoft.com> wrote in message 
> news:7C6C7AA7-19F3-4096-8BD0-EF3C974A1B87@microsoft.com...
> > Can I get a chart to always use say the last 9 values of a series?
> >
> > Does excel also do interactive charts?
> >
> > 
> 
> 
> 
0
Salmonh20 (2)
4/8/2006 6:41:01 PM
Reply:

Similar Artilces:

Data Synchronization Error #2
Errors occrred while going online that prevented some of the data from being synchronized... There are five records with errors... record unavailble and opportunity closed are the errors. The options are 1)stay offline and fix the changes or 2) go online and do not save the changes made to data offline. Is #2 refering to only those records with errors or all data entered while offline. ...

Format HTML Data As Text
We have a database file (linked by odbc) with a memo field that contains html data from e-mail messages. How can we reformat the data as text without the html tags? ...

exporting/linking data from a row on one sheet into another sheet
I have a master sheet with all the sales information that my company tracks. I would like to create seperate sheets for each sales rep. I have all their initials listed under one column. Can I take each row and put the information onto another sheet based on what I put into the initials colum? exporting/linking data from a row on one sheet into another sheet Hi Clark see responses in Excel.worksheet.functions NG Frank Clark Haddock wrote: > I have a master sheet with all the sales information that > my company tracks. I would like to create seperate > sheets for each sa...

How do I change row height to 16.0? I get choice of 15.6 (26 Pix.
In an excel sheet I need the row heights to be 16.0. I select the rows and go to Format, Row, Height and type 16.0. When I check the row height it will be 16.2. I can take the cursor and try to change an individual row height and it will let me choose 15.6 (26 Pixels) or 16.2 (27 Pixels). I have changed the screen resolution and it did not help and I have tried to change print resolution but am only given 600dpi as a choice. Hi AFAIK 1 pixel is the smallest increment you can use. -- Regards Frank Kabel Frankfurt, Germany "jlloyd" <jlloyd@discussions.microsoft.com> s...

automatic list of e-mail subjects
I would like to list the subject names of whole e-mails in my INBOX (including all subfolders of Inbox). How can i do that by code? I think that will be wonderfull, I'm looking for something like that since a year because i can't program anything... I will be even more powerfull if the list could be shared on a server and automatically load to every users. This will sell like cookie since a lot of company work with porject number and custumer, rules for filtering could also be manage on the server by only one person as the subject list also. "mezzanine1974" wrote: &...

Anyway To Always bcc same email address automatically
Is there anyway to set up a rule or something so that everytime I send an email it is bcc'd to a specific email address? Thanks. Rick Bellefond RB Data Services rick@rbdata.com Do not know if this works in 2003 but here is a link to some code that will do this for 2000: http://www.slipstick.com/dev/code/autobcc.htm --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Rick Bellefond asked: ...

Excel 97-remove link but keep data
Hi guys, I have a pre-existing excel 97 s/s which has links to other s/s's. I would like to keep the data only (like paste-special, values) and remove the linked reference from showing in the current s/s. Any ideas how to do this instead of me changing each linked reference? Thanks in advance! search for .xls or [ or ] to see if you can find the formulas that contain links to other workbooks. Van wrote: > > Hi guys, > > I have a pre-existing excel 97 s/s which has links to other s/s's. I would > like to keep the data only (like paste-special, values) and rem...

get number of monday, tuesday,... between 2 dates
Hi, how can I get number of monday, tuesday,... between 2 dates? Thanks, Racsus (Racsus@discussions.microsoft.com) writes: > Hi, how can I get number of monday, tuesday,... between 2 dates? DECLARE @offset int, @day varchar(10) SELECT @day = 'Tuesday' SELECT @offset = CASE @day WHEN 'Monday' THEN 1 WHEN 'Tuesday' THEN 2 WHEN 'Mittwoch' THEN 3 WHEN 'Thursday' THEN 4 WHEN 'Friday' THEN 5 ...

Using Excel 2000 as Data source for Word 2000 document
Problem using Mail Merge using Word 2000 & Excel 2000. After numerous problems - Word failed to connect to Data Souce file then it would & then it wouldn't and so on - reasons which are unknown! The 106 records in 19 fields from the Excel Data Source are only transferring the information for the first 104 records. I am looking to add further records but obviously need to overcome this problem. Hi Malcolm- Obvious, perhaps, but are you certain that the records are in consecutive rows & no vacant columns separating the fields of data? Does the data range contain any Merge...

How to get a development copy?
My company is a Microsoft partner, and we'd like to get a development copy of Dynamics GP. Unfortunately it isn't offered in the Action Pack. Does anyone have any ideas on how we could get a copy of it? Thanks Hi there, There are a few options to get a "development" copy: 1. Get an MSDN subscription. All of the Dynamics products are included in that and you also get the SDK's for each, service pack, and additional development resources. This is probably your best bet. or 2. Sign an return an SPA and commit to becoming certified in Dynamics GP and you will recei...

How keep chart-datapoints displayed when column-width is set to zero
Hello there, my data basis for a chart has one column, that I don't want to be shown in the spreadsheet. However, when I set this column's width to zero, the correspondig data dissapear from the chart. How can I have both, a hidden column and its values shown in the chart at the same time ? Kind regards, H.G. Lamy H.G. - Select the chart, then select Options from the Tools menu, click on the Chart tab, and UNcheck the Plot Visible Cells Only checkbox. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ hglamy wrote: &...

Create a Line Chart
I am having great difficulty trying to create a line chart in Access 2007. I have a report with the following fields: Staff_Component 1QTR 2QTR 3QTR 4QTR Operations 10 28 45 33 Can someone please explain to me how I can create a line chart? Thanks and Happy Holidays! *Glen* The report fields don't mean anything. You can generally add a chart control to your report and use the wizard to set up your chart properties. I expect you will need to update the Row Source property since the wizard often clobbers it. T...

Charting software: what does The Economist use?
Any clue of what software the people at The Economist uses to create charts? I=92m wondering whether there is some software out there better than MS Excel that would give better control without the onerous workarounds. You can do pretty well with Excel, and the "onerous" workarounds aren't too bad once you've become familiar with them. They are generally a bit more onerous in 2007. http://peltiertech.com/WordPress/2008/05/30/magazine-quality-chart-economist/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - ...

Layering Chart Types
I would like to layer a line graph on a second y axis on top of a "stacked column" graph with 3 data series in it. Anyone know how to do this? Create a stacked column chart with all 4 series in it. Then, select the series you want to become a line. Go to Chart, Chart type, and pick a line series. Then, again selecting the same series, right-click, format, axis, move to secondary axis -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jeff" wrote: > I would like to layer a line graph on a second y axis on top of a "stacke...

How to get SUMPRODUCT on filtered cells
I'm currently using the formula =SUMPRODUCT((F2:I1475>=1)*(F2:I1475<=9999)) This gives me the number of the full range of cells but I need this to update whenever I use a variety of filters. Any help is greatly appreciated Sam.D Say we have data in A1 thru B29 like: flia value 0 pass 2 pass 1 pass 2 pass 1 pass 2 pass 0 pass 2 pass 1 pass 2 pass 1 pass 0 pass 2 pass 0 pass 1 pass 1 fail 1 fail 0 fail 2 fail 1 fail 0 fail 1 fail 2 fail 1 fail 1 fail 1 fail 1 fail 2 fail and are appling autofilter to column a. We want to use sumpr...

How to group all data from 1 column into several columns (Redo)
I have 2 columns in my spreadsheet, Company Names and Event. The Event column values are Event 1, Event 2, Event 3, Event 4 and the company names repeat for each of the events that they attended. So sometimes I might have a company listed 4 times with each Event corresponding to it in the next column. How can I consolidate all of the company names so that there is 1 row for each company and all events are on the same row but in different columns marked with an x (the column labels will be Company name, Event 1, Event 2, Event 3, Event 4). This is what I have... Company Name...

Picking Ticket Report
HELP! When orders are entered into the system, the PICK TICKET REPORT (actual name: SOP BLANK PICKING TICKET ORDER ENTERED) is printed to the warehouse for the staff to pick the stock. The way the warehouse layout is setup, all item numbers are on shelfs/drawers in order of item number. Some times when orders are entered, the item numbers are inputted sporadically (nature of the business). So when the PICKING REPORT/TICKET is printed, the items are in order of input. We would like to have the ticket/report print the items by order of Item Number. Apparently this was done in the past by...

How to automatically re-order a list
Hi, I have a worksheet with data in several rows. Column wise they consist of names, points etc.. I am trying to setup a formula whereby the formula looks at the data in the points columns and then re-orders the rows accordingly taking all the information in the row with it, with the highest points total at the top and lowest points total at the bottom. Each points cell, has a formula in there already which pulls the points total from other cells and combines them. Hope you can help! Thanks sandmania, Have your tried Data / Sort? Select the whole table, and the rows will stay togeth...

Pivot Charts Display Filter Box
I would like to have the filter box displayed on my pivot chart. My 2003 pivot charts no longer display the drop down box, how do I get it back? ...

Automatically open different workbooks depending on user account used.
There are two userrs on the same computer. When user 1 opens Excel, I'd like oen workbook to automatically open. When user2 opens Excel, I'd like a diffferent workbook to automatically open. Is this possible? (Each user logs into Windows usning their uuser account.) The computer is running windows XP pro and Excel 2007. Thanks, Something like this might work for you. This is untested. Post back if this doesn't work for you. Place the following macro in the ThisWorkbook module. You would need to change ThePath to your real path, the user account names for Joe and Bill...

Many to Many Data Entry
Hi all, This is a doozy of a problem, using a form to join many to many fields, so thanks if you're up for checking it out! I have two tables that are linked with a many to many relationship using a third table. TblZones (with fields ZoneNo(primary key, autonumber), ZoneMo, Zone Description, CustNo, SiteNo, etc) TblDevices (with fields DeviceNo (primary key, autonumber), DeviceType, Location, CustNo, SiteNo, etc) TblDeviceZoning (With fields ID (primary key, autonumber), DeviceNo, and ZoneNo) A device may only be assigned to a zone that shares the same customer number...

Data sheet populating into multiple sheets
I'm looking to build a spreadsheet with one main page that can be populated with data and automatically pull that data over to other more specific sheets. For example, If I have sales on the main sheet and have check boxes for more specific sales on the same sheet (i.e. liquor sales, food sales etc..) I want that number to poulate on the specific sales sheet. I'm assumming I have to wite an IF/Then Statement to say if an "x" is put into a cell then copy and past the data in the specified cells within that row and pull that information over to sheet Y. One respon...

MNY 2006 Trial
Notice, I'm asking about Money 2006 Deluxe downloaded trial edition. I have set up my accounts (bank, broker, credit card) for automatic updates. If I manually "update accounts" it does (most of the time). But it never "automatically" updates the accounts. I have the banks on the default, no changes, Every Day setting. I've read the MS MNY Help topics, so please don't direct me to them. What else might I be missing? MEP ...

charting a single bar graph on one line showing continuous info
I must do a time graph showing total workhours with tick marks showing begin and end of tours on a dialy basis. I cannot get the custom or standard chart graph functions to do this without putting in all of the data. I just want the begin tour and end tour of each person. can you help. thx debi ...

Overlay 4 years of data as a line on 4 years of columns for several x category labels
Dear Charting aficianados, The tutorial at http://peltiertech.com/Excel/Charts/ClusterColAndLine.html is the closest example I can find to a charting conundrum I am facing, though what I am hoping to achieve is still a little different. I've made a mock up of what I'm hoping to achieve - though I have had to use drawing objects for the line series, to overlay on the columns - I'm sure there is a way to get excel to do this - do you think there is? (sheet named mock-up) A workbook with mocuk-up and my other charting attmempts can be found: http://www.savefile.com/files/2690840 T...