Charting depending on criteria & data series name as a column val

I want to chart some prices as I want to take a look at price trends.

My problems, and I can not figure out how to solve them, are:
1. Is it possible to dynamically change the chart depending on certain 
criteria (product family & selected customers)
2. As the number of customers is variable and they are in one of the 
columns. Is it possible to plot a series (customer name), depending on the 
name of a column?
3. I want to chart the data based on the date, but just include the dates 
available, to prevent periods of time showing no change (e.g. I don't want 
monthly ticks for the x axis when I have two different prices, one for 2007 
and other for 2009) I think I can define the X Axis as category, but the 
mixed up customers and dates, it doesn't work, unless I just have one 
customer.

Here it is an example of my data:

Product Familiy	Customer Name	Price	Date
A1	                A              	1	01/01/2008
A1	                A              	2	01/03/2008
A1	                A              	3	01/05/2008
A1	                A              	4	01/07/2008
A1	                A	                5	01/09/2008
A1	                B	                1.2	15/02/2008
A1	                B	                1.8	15/04/2008
A1	                B	                1.9	15/06/2008
A1	                B	                2	15/08/2008
A1	                B	                2.5	15/10/2008
A1	                C	                1	25/03/2008
A1	                C	                1.5	25/06/2008
A1	                C	                2	25/09/2008
A1	                C	                2.5	25/12/2008
A1	                C	                3	25/03/2009

Thanks,
0
MrLuckyMe (5)
6/15/2009 4:35:01 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
441 Views

Similar Articles

[PageSpeed] 46

It sounds like you're wanting to use dynamic charting. To do this, you're 
going to need to become familiar with using named ranges and possibly some 
control boxes. I'd recommend taking a look at Jon Peltier's site:

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

He has several pages dealing with various types of dynamic charting.
-- 
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Mr.LuckyMe" wrote:

> I want to chart some prices as I want to take a look at price trends.
> 
> My problems, and I can not figure out how to solve them, are:
> 1. Is it possible to dynamically change the chart depending on certain 
> criteria (product family & selected customers)
> 2. As the number of customers is variable and they are in one of the 
> columns. Is it possible to plot a series (customer name), depending on the 
> name of a column?
> 3. I want to chart the data based on the date, but just include the dates 
> available, to prevent periods of time showing no change (e.g. I don't want 
> monthly ticks for the x axis when I have two different prices, one for 2007 
> and other for 2009) I think I can define the X Axis as category, but the 
> mixed up customers and dates, it doesn't work, unless I just have one 
> customer.
> 
> Here it is an example of my data:
> 
> Product Familiy	Customer Name	Price	Date
> A1	                A              	1	01/01/2008
> A1	                A              	2	01/03/2008
> A1	                A              	3	01/05/2008
> A1	                A              	4	01/07/2008
> A1	                A	                5	01/09/2008
> A1	                B	                1.2	15/02/2008
> A1	                B	                1.8	15/04/2008
> A1	                B	                1.9	15/06/2008
> A1	                B	                2	15/08/2008
> A1	                B	                2.5	15/10/2008
> A1	                C	                1	25/03/2008
> A1	                C	                1.5	25/06/2008
> A1	                C	                2	25/09/2008
> A1	                C	                2.5	25/12/2008
> A1	                C	                3	25/03/2009
> 
> Thanks,
0
LukeM (202)
6/16/2009 1:26:01 PM
Luke,

Thanks for your help.

I think my case is different. Usually for charting you use a column for 
X-Axis and the series are identified as the headings of different columns 
containing the data to be charted. In my case I have the X-Axis defined as 
column, the values are in columns, BUT the series names are in columns, they 
are not the heading of different columns.

I took a look at the Jon Peltier's site and he addresses the dynamic charts, 
in my case the data is constant, the X-Axis is depending on dates that I can 
define as a category (Chart options, X-Axis).

If a define the series sorted out by customer, I can get very easily the 
right name for each series, but the dates are mixed up, if I sort the dates 
the customers are mixed up and it is harder to define manually the series.

Any hint?

Thanks,

"Luke M" wrote:

> It sounds like you're wanting to use dynamic charting. To do this, you're 
> going to need to become familiar with using named ranges and possibly some 
> control boxes. I'd recommend taking a look at Jon Peltier's site:
> 
> http://peltiertech.com/Excel/Charts/Dynamics.html
> 
> He has several pages dealing with various types of dynamic charting.
> -- 
> Best Regards,
> 
> Luke M
> *Remember to click "yes" if this post helped you!*
> 
> 
> "Mr.LuckyMe" wrote:
> 
> > I want to chart some prices as I want to take a look at price trends.
> > 
> > My problems, and I can not figure out how to solve them, are:
> > 1. Is it possible to dynamically change the chart depending on certain 
> > criteria (product family & selected customers)
> > 2. As the number of customers is variable and they are in one of the 
> > columns. Is it possible to plot a series (customer name), depending on the 
> > name of a column?
> > 3. I want to chart the data based on the date, but just include the dates 
> > available, to prevent periods of time showing no change (e.g. I don't want 
> > monthly ticks for the x axis when I have two different prices, one for 2007 
> > and other for 2009) I think I can define the X Axis as category, but the 
> > mixed up customers and dates, it doesn't work, unless I just have one 
> > customer.
> > 
> > Here it is an example of my data:
> > 
> > Product Familiy	Customer Name	Price	Date
> > A1	                A              	1	01/01/2008
> > A1	                A              	2	01/03/2008
> > A1	                A              	3	01/05/2008
> > A1	                A              	4	01/07/2008
> > A1	                A	                5	01/09/2008
> > A1	                B	                1.2	15/02/2008
> > A1	                B	                1.8	15/04/2008
> > A1	                B	                1.9	15/06/2008
> > A1	                B	                2	15/08/2008
> > A1	                B	                2.5	15/10/2008
> > A1	                C	                1	25/03/2008
> > A1	                C	                1.5	25/06/2008
> > A1	                C	                2	25/09/2008
> > A1	                C	                2.5	25/12/2008
> > A1	                C	                3	25/03/2009
> > 
> > Thanks,
0
MrLuckyMe (5)
6/16/2009 1:53:01 PM
Reply:

Similar Artilces:

Data Series Labels in Area Chart
Hello- I'm trying to create a combination chart; one data series in an area chart, and three in line format. Got the chart created, but the data series labels for the area chart show up in the middle of the area, which looks awkward. Is there anyway to move them closer to the top edge of the area plot? I don't seem to find any options for moving the labels in an area chart. Hi, The data labels for the area series have no options for alignment. Add another line series to the chart based on the area data. This has the options to align data labels. You can format the line seri...

Sending the variable to a query as a criteria
Hello, I can send a value as a criteria to a specific query if it is a text of a textbox, combobax, label etc. But, i wonder if i can send a variable to a guery... I mean, such as... dim a as integer a= me.texbox3.value requery xxx... And in xxx, there should be column, for ex. xyz that has the criteria a.. But it does not work :( How can i do it? thanks... SupperDuck wrote: > Hello, > > I can send a value as a criteria to a specific query if it is a text > of a textbox, combobax, label etc. > > But, i wonder if i can send a variable to a guery... Create a us...

Copy Pivot Chart
Does anyone know how to copy a pivot chart and paste only its values? You can copy/paste special/value as you would any other cell values. However, you need to navigate from "edit" on the tool bar. These options are not available for a pivot table via the right click. "TK" wrote: > Does anyone know how to copy a pivot chart and paste only its values? ...

Retrieving Data from a Column / Row to populate a cell
I currently have data supplied to me in an Excel spreadsheet that I to transfer manually I would like this to populate a cell reference, the problem I have is that I cannot gather a train of thought to obtain the data from from a specific row/column. There is a sample of the data supplied to me below Name In Out Int Greg 165 108 29 09 July 2003 42 34 9 14 July 2003 44 19 4 16 July 2003 40 40 8 17 July 2003 39 15 8 Andrew 125 43 9 1...

Copy Cell or Column without advancing formula reference?
I have a spreadsheet with formulas referring to another worksheet. I need to copy the columns however, each time I do, the formulas advance to another cell. Which is an incorrect reference, skewing my results. You need to modify the formulas to use absolute references. Edit each of the cell addresses in the formula and put $ (dollar signs) in front of the column letters. "sgluntz" wrote: > I have a spreadsheet with formulas referring to another worksheet. I need to > copy the columns however, each time I do, the formulas advance to another > cell. Which is an...

How do I add a data series in a combination chart in PPT 2003?
I have a combination bar/line chart showing gross revenues and passengers by month over a 3-year period. The gross revenues are bars on the primary y-axis and passengers are lines on the secondary y-axis. I am trying to add gross revenues and passengers for CY2010. I am able to add a data series for the passengers, but when I try to add a data series for the gross revenues, the chart treats it as a series on the secondary axis as a line. How do I add the series as a bar on the primary axis, or alternatively, change the series from a line on the secondary axis to a bar on th...

The data in my form is changing after the status is closed
I have a form which is based on a query for invoicing. I have some client who have been invoiced for their service and not their status is closed. If this person returns as a client and I set the new contract year and I go to the invoicing form to invoice for their new serivce the prior year contract year information for the service which has already been closed is no longer there. I want to keep this data as read only, but still be able to add new information. ...

Independently Formatting Columns and Rows
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Is there a way to independently format the width of a column on specific cells? In other words, if I wanted to change the width of the cells in the middle of the document, can I do so without affecting the width of the cells above it and below it? On 2010-05-28 12:40:38 -0400, monks617@officeformac.com said: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: > Intel Is there a way to independently format the width of a column on > specific cells? In other words, if I want...

Print Footer with Last Print Date field name
How do you create a Footer to print the Last Printed Date and Last Revised Date? Its not as obvious in Excel as it is in Word. Hi this is only possible with VBA inserting this information within an event procedure -- Regards Frank Kabel Frankfurt, Germany Ted wrote: > How do you create a Footer to print the Last Printed Date and Last > Revised Date? Its not as obvious in Excel as it is in Word. ...

Org Charts
I have 5 people that report to the same person and currently they all show on the same row, the owner of the company wants the 3 VPs to show directly underneath him but the remaining two to have their line drop down lower than the 3 VP's. IS there a way to do this? Everytime I try it just makes the box bigger for all of them. Hi, Are you doing this in Excel? Which version? Which tool the Diagram tool or the AutoShapes tools? -- Thanks, Shane Devenshire "rgoldberg" wrote: > I have 5 people that report to the same person and currently they all show on > the sam...

formatting or moving a radar chart's axis labels
I am attempting to change the orientation of a radar chart's axis labels. Its orientation seems locked in place immediately to the left of the 360=B0 gridline. I would like the ability to display the axis labels directly over the range rings and along other gridlines, such as 180=B0 or 270=B0. With other charts, the formatting option allows one to select "high", "low", or "next to axis" to change the position of the axis. This maneuver seems to have no affect whatsoever on a radar chart. As a final resort, I suppose I could turn the axis labels off...

Naming a chart object
When charts are created they are named "chart 1", "chart 2", and so on I need to name a chart in order to update it from vba, but when select the chart I cannot click in the name box? Help -- Message posted from http://www.ExcelForum.com Hi martinu, To rename a chart, hold the shift key down and select the chart. This selects the chart object instead of the chart area. You can then rename as usual. -- Ed Ferrero http://edferrero.m6.net Thanks Ed, so simple when you know how. --- Message posted from http://www.ExcelForum.com/ ...

merged cells and creating charts
I have tried to create a chart with data that is in merged cells where I have put in a formula to calculate the average of 4 cells in the row above. When I create the chart, it recognizes all 4 of the merged cells. So the graph has 4 spaces for the data for each number I am trying to plot. I just got 2007 version and do not recall this problem at all in 2003 version Merged cells are to be avoided at all costs. They can be used for column heading but for little else. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Troubled...

Chart disappears in Excel 2000 after scale is changed
When the scale of the chart is changed, the chart disappears. The chart and its data are on the same worksheet. ...

Prevent cells with no value from being plotted on chart
I have a chart based on cells that are the result of a calculation. The cells show empty if the data used for the calculation is not yet available (future data). As a result, as my chart shows the whole year, the line drops and remains to zero after the last available set of data. Excel will let me prevent an empty cell of being plotted but since there is a fornmula is the cell, it is not considered empty, and therefore displayed. How can I prevent these cells to be plotted on the chart? Thanks Vince You can use an IF formula with NA(). For example: =IF(B2="",NA(),B...

Find name of main form
Novice, Access 2003, XP I want to pass a control name to another application. The control is on a subform, but I don't know the name of the main form because the subform is in various mainforms. E.g. fsubTxt contains txtBox1. How do I find the name of frmMain and then pass the frmMain!fsubTxt.Form!txtBox1 Thank you You can get the main form's name by using Screen.ActiveForm.Name Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Peter Stone" <PeterStone@discussions.microsoft.com> wrote in message news:20BA9DE7-0107-46CB-96F8-591...

Adding Lines Onto Charts #2
Hi, Thanks for the reply, Unfortunatley, this technique wont work with the way I have the graph laid out. It wipes out the layout of the bottom axis. Does anyone have any other idea's please? Thanks in advance Darrell... -- q582gmzh ----------------------------------------------------------------------- q582gmzhi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1136 View this thread: http://www.excelforum.com/showthread.php?threadid=26950 Hi, You can use an additional data series with error bars to draw the dividers. Using you example workbook put t...

Logarithmic charts
How should the data be entered in a logarithmic chart. I want the log data entries to be on the left and the other data to be displyed on the bottom. My chart will give a logarithmic resistance vs RH. I have tried to list the data by columns left to right first the resistance then thRH for 4 different series. It just doesn,t look right and I cannot change anything easily. Are there any sources for making charts in excel ,especialy Logarithmic ones? Thanks for any help? Hi Ed, In A1:A10 enter your resistance values (the x-values) In B2:B10 enter the RH (meaning?) values (the y-values) Sel...

2axis chart
Hi, I have a tricky chart I want to make. So far it looks okay, but I have a problem that I hope is fixable. I have 5 data series that I want to represent in a graph with 2 axis. I want to use the stacked column type of graph putting 2 series each in one column. ie series = Actuals1, Actuals2, Budget1, Budget2, Last years totals. I want 1 stack showing Actuals1 & Actuals2 together, another showing Budget1 & Budget2 together and a line showing last years totals(I'd prefer another seperate bar for this on it's own, but can figure out how to do this). So far I have got...

How do I clear the formating of the area OUTSIDE my chart on my w.
...

visio 2007 columns and relations disappeared
I reverse engineered a database and at some point the wile I was organizing the tables a few of the foreign got left behind. I spent long time organizing the tables and found this very frustrating. I could not find a way "reattach" the tables to each other. I removed the orphaned lines from the page thinking that when I updated the diagram from the database they would reappear. They did not. In fact columns had also been removed from tables. How do I get the tables to reflect the data in the database again? Refreshing the model does not work! ...

repeat data as seperate tables
Hi, Does anyone have a solution for a problem i'm strugling with? I would like to repeat some data from a collection of objects. Using a DataList while generate 1 table as a result. However, I would the output to be a seperate table for each repeated item. Can this be done? Thanx John On Nov 26, 1:27=A0pm, "John Devlon" <johndev...@hotmail.com> wrote: > Hi, > > Does anyone have a solution for a problem i'm strugling with? > > I would like to repeat some data from a collection of objects. > Using a DataList while genera...

How to create chart basic
How to create a scatter chart with spread sheet below. The x axis may be location and Y axis are lot name Lot value location A 3 1 A 4 2 A 5 3 B 6 1 B 4 2 B 2 3 C 5 1 C 6 2 C 8 3 D 4 1 D 9 2 D 5 3 Daniel, Highlight the whole chart of data. Select the Chart Wizard on the top of Excel screen. Select the scatter plot. Data should be "rows" not "columns". Select the "Series" tab. "Name:" ="A" "X Values:" =Sheet1!$B$2:$B$4 "Y Values:" =Sheet1!$C$2:$C$4 This assumes that your data is in column A thru C, Lot in A1, value in ...

make chart based on equation
How can I make an excel chart based on an equation? On Fri, 10 Aug 2007, in microsoft.public.excel.charting, Daniel <Daniel@discussions.microsoft.com> said: >How can I make an excel chart based on an equation? Your equation will be in the form y=f(x). Create two columns, one of which is filled with values of x, and the other filled with copies of the equation f as an Excel formula, referring to the x values. Now make an XY Chart using these two columns. -- Del Cotter NB Personal replies to this post will send email to del@branta.demon.co.uk, which goes to a spam folder-- ...

Create set based on column items feature in Pivot Tables 2010
I have a pivot table created in 2010 that I would like to use the 'create sets' feature in. However it is not enabled. The three 'sets' features are disabled as are the OLAP tools / What if analysis options on the Pivot Table options ribbon. What am i missing??? Cheers Julie Okay, found the problem - for 2010 the sets are only allowable for OLAP based Pivot Tables. :( On Nov 23, 10:16=A0am, jigsaw <jul...@consultant.com> wrote: > I have a pivot table created in 2010 that I would like to use the > 'create sets' feature in. =A0However it is not enabled....