Line Chart With Variable Data Series
Jan 17, 2014
I am trying to produce a line chart, which will use imported data on a separate sheet (but within the same workbook)
The imported data can be of different sizes ie column widths and length. Some columns may contain only zero and should not be included in the chart, and will be to right of the data.
I have created a named range for the data series called ChartData using =OFFSET(Data!$C$1,0,0,info!$C$7,info!$A$2) - C7 and A2 are calculations to set column and row sizes. I have tried to use this as the chart's data series, but without success.
View 4 Replies
ADVERTISEMENT
Feb 15, 2007
Im plotting data on a Line-Column 2 Axes Chart which works really well with 4 data series (2 column, 2 Line) but when adding a fifth data series, the fourth disappears from view. I can see its still there cos I can display the values but I cant get the line to appear. The values for the 5th series are also similar to other series values so its not like the chart cant display it....are there limits to the number of series on such a chart?
View 9 Replies
View Related
Dec 22, 2011
I have the following code:
Sub Macro5()
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(2).Delete
End Sub
However, if there is no SereisCollection(1) actually present in the chart I get an error. How can I work around this? I will need an IF statement I assume, just not sure what it will look like.
View 4 Replies
View Related
Oct 5, 2006
I want the below code to run from R1C10 (i.e. cell J1) to the end of the data in the row, which will vary in position, rather than having to define where the end of the row will be beforehand (i.e. R1C21).
Is this possible?
View 9 Replies
View Related
May 23, 2008
I have data which is imported from a txt file and always starts at the same row (R19) but is variable in length (end of data R??). I want to be able to select the chart xvalues (R19C10:R?C10) and values (R19C4:R?C4) based on the number of cells with data in them for a given column. In addition I believe I'm having troubles with sheet references, when the txt file is opened the sheet name is set to the file name, since I want to use this macro on many different files I need the sheet referencing code to account for this. Anyway here is the section of code which seems to be giving me trouble (the initial sections just create a new column of data which needs to be referenced as the xvalues) ...
View 3 Replies
View Related
Jan 8, 2010
I have data:
Jan Feb Mar Apr May
2009years 56 45 42 50 62 - series1
2010years 48 47 45 48 55 - series2
I need make line chart from 3 series. First two are series1 and series2. Third is percent change between 2010 and 2009 years every month (series3). First two are easy, but third is very difficult for me, becouse I haven't row with percent change and I can't haven't it in my exercise.
How can I make it without new row with percent change?
I tried it with Data Source in chart, also formula in Name Manager, but it doesn't succeed
View 9 Replies
View Related
Aug 25, 2006
I have a line Chart and want to create a static horizontal/indicator line.
I cannot create a new series to plot this horizontal line as I'm using an external data source (OLAP cube) as source data for the chart.
View 8 Replies
View Related
Oct 2, 2006
I am creating a line chart with markers using 5 series of data. I want to colour in/shade the area under the lowest series.
View 6 Replies
View Related
Sep 3, 2007
Is there a way to add comment text at a 45 degree angle to an excel chat?
What I need to do is to add a comment parallel to a 45 degree line on an excel chart, and the text box dosen't give you the option t have 45 degree text.
View 6 Replies
View Related
Oct 2, 2012
I'm attempting to create a dynamically built donut chart using VBA, but I'm having trouble inserting a variable to be used as one of the values.
The chart graphs 3 data points:
ASC GP - Located in cell T4
ACC GP - Located in cell AE4
ACT GP - variable called LocSumActGP
The code snippet I assumed would work looked like this:
Code:
.SeriesCollection(1).Values = "='Location Summary'!$T$4,'Location Summary'!$AE$4," & LocSumActGP
But it doesn't seem to like that.
View 2 Replies
View Related
Apr 15, 2014
I want to add a user-form on a chart, which will have check-boxes that will allow me to select series(lines) that I want see and compare in a chart. Currently my line chart has 24 series (Lines) which makes the chart very difficult to view and looks very busy. How do I add list of check-boxes that will allow me to select one or multiple lines that I want to see at a time?
View 2 Replies
View Related
May 31, 2008
I just recently installed Excel 2007 and I would like to know if it's possible to change all data points of a chart at the same time. In Excel 2003, I would normally hold down shift while clicking on each of the data points to make a global change. However, it appears I cannot do that in 2007.
I would like to display each data point's series name. When I go to Layout on the Excel Ribbon, and click on "Data Labels", and click on "More Data Label Options", the actual Y-axis values are shown for each data point. However, I do not want this - I actually only want the Series Name, but when I uncheck "Value" and check "Series Name" instead (under "Label Contains"), it only changes it for one of the series. Is there a better way, instead of going through each and every single series to make this change?
View 4 Replies
View Related
Jun 30, 2008
I have a chart with 3 data series. The series are located in columns A, B and C respectively. Series I is a general number anywhere from 0 to 100,000. Series 2 is also a number, but is is devided by series 1. So, if series 1 was 100,000 then series two would be 25,000/100,000 which is 0.25. Series three will always be numerator of series 2 or 25,000 in this example. The numerator, or the 25,000 will never change, so, in my example, series three will be a straight line across the chart because it is always 25,000.
In my example, the .25 is plotted on the left value axes, and series one is plotted on the right value acccess (secondary axis). Series three is just a line in the middle.
My problem is that sometimes the line, series 3, doesn't match the values in both the right and left values axes. So, if series one is 48,000, then series two would be 48,000/48,000 = 1, and series three would be 48000 - the straight line. The third series should be a line touching the 48,000 on the right and the number 1 on the left. It does touch the 48000 on the left, but is below the number 1 on the left.
View 9 Replies
View Related
Nov 27, 2011
I am looking for a creative way to display a pie chart within a data point marker of a line chart.
My database has 3 value columns, Type1, Type2 and the Total (Type1 + Type2)
these are recorded per day (Date, in Column A)
I have a line chart that displays the total by date, but I want to find a way to display the percentage split of a particular day by type.
I was thinking to load the chart image into the Data point marker, but i don't think that is the best way as the data is updated daily and I would have to do it each day for a few line charts.
The other way I was thinking about was to have a generic Pie chat in the Line chart (Maybe in a corner) and the pie would update depending on way date series was selected or Mouseover'd)
the way that I am approaching it at the movement (Not the best way and by far not the coolest way. Is to have a list of all the dates in a column next to the Line chart and using some VBA, what ever date is selected in the column the pie chart displays the corresponding data. But eh challenge is that when there is alot of dates, I am going to be scrolling up and down.
I am using Excel 2010, but I cant not use the slicer's as the other users do not have 2010, they have 2007.
View 1 Replies
View Related
Sep 27, 2011
I am trying to create a line graph that will incorporate multiple columns of data in one series of data. The reason I do not place all of the data in one column is because it could exceed the maximum amount of rows allowed in excel. Also I need the data split up for viewing purposes.
I can easily just graph one column but how do I combine all the columns into one line graph with the data being in separate columns. Basically all the columns will be my Y values and X values are just 1:n.
Example Below:
Column AColumn B Column C159261037114812
Now in the example all of the values are x values.
View 2 Replies
View Related
Nov 9, 2006
Trouble: I need to replace address of datacells for serias of chart
Result: data for serias will be some lines above or below as i need
simv = Workbooks(1). Names("adr").RefersToRange.Formula
num = Len(simv)
For i = 1 To ActiveChart.SeriesCollection.Count
oldadr = ActiveChart.SeriesCollection.Item(i).Formula
res = Left(oldadr, Len(oldadr) - num - 3) + simv + Right(oldadr, 3)
ActiveChart.SeriesCollection.Item(i).Formula = res
Next i
simv - my new address data for all serias this code is working on Excel 2003, but not working on some other versions. Beause "Formula" may has different structure :
version 2003: =SERIES(,,'C:...[macros.xls]Sheet2'!$B$8:$M$8,1) other version:
=SERIES('C:...[test.xls]#Source'!$C$6:$D$6,'C:...[test.xls]#Source'!$E$2:$AC$2,,1)
View 3 Replies
View Related
Oct 25, 2007
I have a chart with Data Series being dynamic ranges. There is only one series and x & y each have a dynamic range. I need to be able to include Series Name as a dynamic range.
For example. I have 3 rows of values with 3 columns.
Col 1 Col 2 Col 3
Name X-Val Y-Val
A 1 3
B 4 6
C 8 0
I now need a chart that has 1 series with a x range of col2 and a y range of column 3 but a corresponding name equal to Col 1. So when I move my mouse over each point I get the series name. E.G. I hover over poing 1,3 I show the name as A. Or I hover over point 8,0 I get C.
If it isnt possible using the ranges the way I am, then I would be looking for a possible solution. I am sure I could do this by some sort of loop but not quite sure given the number of rows constantly changes.
View 7 Replies
View Related
Aug 4, 2014
I run a large simulation experiment. I have a loop plotting data in excel of a user defined area. Because of the limit of 255 series I have allowed a maximum of 250 simulations (they all need to be plotted). But the length of each simulation is free. I know there is a limit of 32.000 data points in a graph and I have this as a condition too.
If I set the data range to 100 columns and 3000 rows the graph is produced when I plot by columns. (code below)
But if I set the data range to 250 columns and 1000 rows I get the above mentioned error message. Even though I only have 250 series.
After the data is plotted it is the code below that gets the error:
[Code] .....
View 1 Replies
View Related
Sep 16, 2006
is it possible to reference part of a data series from a chart to a combo box?
View 3 Replies
View Related
Jun 28, 2008
I think that this is probably rather simple, but the answer is eluding me, I cannot find anything that helps online either. I have a set of data that looks similar to the sample below, however it is quite a bit longer. I would like the 'Effect' as the X axis, with the 'Score' as Y. This basically shows three columns, which is fine. However I would also like to use the 'ID' as the label against each plot point and I'd like each plot point to look the same.
The problem I am having is that to get the labels to show, I am having to add each series manually, which is rather time-consuming. Is there a correct (faster) way of doing this as I think I'm missing something obvious!
IDScoreEffect
12.11
21.92
31.33
41.81
51.53
61.72
72.33
81.42
92.21
102.13
112.12.................
View 2 Replies
View Related
Aug 21, 2006
The following code is supposed to produce six series on an xlXYScatter chart. It produces seven with the seventh series being a repeat of the sixth but named series 7.
Sub Chart2()
Dim DataRange As Range
Dim CellString As String 'Stores a cell range in the form "AA27:AB39"
Dim CurrentSeries As Integer
Dim SeasonCount As Integer
Worksheets("Hemisphere").ChartObjects(2).Activate
CurrentSeries = 1 ............
View 9 Replies
View Related
Jun 24, 2008
I have a list of data on one sheet and a 'reports' page on another sheet. The reports page has several pivot tables and a pivot chart. I want the pivot chart to format the bars on the chart relevant to the series name. The series names are "R" "A" amd "G" for Red Amber and Green respectively, I want the chart to change the colour of the series so that it is the correct colour ie. "R" would be coloured Red, "A" would be Amber and "G" would be green.
Sub PivotLoader()
Dim Red As Integer
Dim Amber As Integer
Dim Green As Integer
Red = Range("H9").Value
Amber = Range("H10").Value
Green = Range("H11").Value
Range("B8").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Range("D25").Select
ActiveWindow.SmallScroll Down:=18
Range("B49").Select...........................
View 2 Replies
View Related
Jan 15, 2014
I'm trying to use some defined names to create charts. Though it works at the very begin, some extremely weird things happen when I try to develop further .....
Attached please find the test file, which simplifies my real case but shows the same problem.
Test.xlsx
In the test file, three names are defined as below:
Line1: "=Xvalue*1"
Line2: "=Xvalue*2"
Xvalue: "=ROW(SampleChart!$A$1:$A$25)"
the chart is very sample: "=SERIES(,Test.xlsx!Xvalue,Test.xlsx!Line1,1)"
So far, it works very well. However, I just simply can't add the second line into the chart, for example, "=SERIES(,Test.xlsx!Xvalue,Test.xlsx!Line2,2)"
I canNOT even change "Line1" to "Line2" in the "SERIES" formula!
View 5 Replies
View Related
Jan 13, 2010
Currently I'm working in an excel file that contains a lot of charts. I want to write a macro that changes the source data of every serie in every chart. It only needs to add extra rows. But as every series has different columns where they get the data from, how can I change the rows without changing the columns?
This is what I've written so far:
Code:
Sub ammend_datarange_series_all_charts()
Dim start_cell As String, end_cell As String
Dim chart_tot As Integer, c As Integer, s As Integer
[Code]....
As you can see I've tried two things, first I tried to change the string of the source data, however this meant that every series would have C and E as their source data. So I wanted to retrieve the x and y values in string format. This does not work yet, it returns an array with the values of all the entries in the series source data. From that array I can not retrieve the column where the data came from.
View 4 Replies
View Related
Jul 15, 2013
At the moment I've got a chart with 3 series lines on it and each line has 12 points. I need to select the 12th point on each series in the chart and create a label for it. The 3 lines are Budget, Forecast and Actual and often the Forecast line hits the same points as the Budget line in the chart and every time I try to find a way to select the last point on the Forecast line, I end up selecting the budget one because it's the one in the foreground.
I can select the forecast line with the keyboard arrows, but not the last point.
I also try to zoom in to get it but one of my charts has exactly the same forecast line as budget line and I still need the forecast line because the forecast will change in future.
I can delete the budget line then add it back, but this is difficult and one I'd have to repeat many times because I'm always making graphs and labelling the final point in a series is something I'm going to have to do more often.
How do I select the last point on the forecast line if it's directly behind the budget line?
View 4 Replies
View Related
Sep 20, 2013
My code inserts charts and then assigns them data series. The problem is that sometimes it likes to grab 5 or 6 extra sets of data series and throw them in there too. I've done some research and it seems that Excel likes to guess what data is going in to a chart when you first add it in and grab some of that, rather than just waiting for me to assign it the data. The sheet I'm doing this on has multiple sets of similar data along row 1, the code crawls row 1 and creates a new chart for each set of station data it finds.
Code:
Sub Make_Chart()
'create bar chart
'prep
Set chrt = ws.Shapes.AddChart.Chart
'add and format chart
[Code] ........
Example data set below. RefCell is set to the top left cell (4.02), n is the length of the raw data in rows.
4.02
ON
OFF
2
0:36:10
0:38:03
0:01:53
[Code] ..........
View 3 Replies
View Related
Oct 8, 2013
I have a database where I would like to plot graphs from data of different periods and different series.
My current method is to go to the data set formulas and changing data range. The tricky part is that I might skip data series because I am selecting the series required for each chart.
How to do plot the different graphs efficiently and quickly.
View 2 Replies
View Related
Apr 8, 2008
I am looking to create a number of series on a chart (xyscattersmooth) from a datatable. Below is my data Table....
View 9 Replies
View Related
Nov 24, 2009
I am trying to create a line graph and I have 17 rows of data for 17 different employees. There are 18 rows when including the header row.
My data is as follows
Column A - Employee Name
Column B - Month 1 data
Column C - Month 2 data
Column D - Month 3 data
Column E - Month 4 data
Column F - Month 5 data
When I highlight the data and go to the chart wizard, I select "Series in Rows".
However, on the series tab it only brings in the data for 6 employees.
Is there a way to automatically bring in all the data without having to do it manually, or is this a limitation of Excel?
View 9 Replies
View Related
Jun 9, 2006
whilst trying to format a data series in the attached chart, i cannot find markers. on another chart when i format data series, patterns, i can edit lines and markers.
View 6 Replies
View Related
