Chart: Data Series Address
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
ADVERTISEMENT
Sep 15, 2007
I am trying to create an XY scatter plot with multiple series. Normally, one has to manually type in or mouse-drag a range for each x series and y series. Instead I would like to build these two ranges (x and y) with text functions and place them in a cell.
For example:
A1: 'data'!$C$3:$C$10
B1: 'data'!$D$3:$D$10
Then, when I create a chart, instead of mouse-dragging, for the x-series, I'll indicate the range that's specified within Cell A1, and for the y series, that which is in B1. I would then repeat this for each series. The reason I want to do this is because I have dozens of series and dozens of graphs and it is relatively easy to programatically identify the range boundaries, but long and tedious to mouse drag them.
View 3 Replies
View Related
Apr 23, 2008
Looking for code that returns the cell address of a series in a pre-existing graph. I have a graph that already has Source Data manually assigned to it. Is there a way to find just the cell values or location of a single Series in a SeriesCollection object? I know that in order to assign a series of data to a Series object, you do something like this:
ActiveChart.SeriesCollection(1).Values = Worksheets("My Worksheet").Range("A1:F1")
The issue I am tackling with is a graph that already has preexisting data in the Series object, and I just need a quick and easy way to find the address of this data set.
View 3 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
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
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
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
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
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
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
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
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
Aug 30, 2006
Attached please find two charts based on data from 2003 until year-end 2006 (estimated). I want to show that the data is "seasonal" and sales depends on the month it falls on... i have several issues with the chart, is the second chart "too" overlapping and is there too much data to read? 2) how can i get the text boxes to go in the same direction as the chart..i know in powrerpoint you can move the direction of the textbox but how in excel? in sum, i can only choose one chart but still want to make each chart look much better.
View 3 Replies
View Related
Nov 30, 2006
I am attempting to use a dynamic named range in a chart data series as described in Excel Hacks (Hack #42 and Hack #52).
Using Insert|Name|Define I have created a named range called CashFlowSaleChartDataRange that is set to the following value:
=OFFSET('Cash Flow-Sale'!$O$10,0,0,NumFlows,1)
When I subsequently assign a reference to this named range in the data series dialog it will accept the answer and my chart will adjust to reflect the updated range. However, if I return to the data series dialog, I now see the range address returned by the OFFSET function rather than the named range reference.
View 2 Replies
View Related
Jan 16, 2008
My friend has set up an excel workbook with a sheet for every month (labelled Jan graphs, Feb graphs, Mar graphs etc) which has 7 pie charts per sheet. The problem is the pie charts are showing the 0% fields and the data labels are overlapping. I have found a macro that gets rid of the 0% labels but I need to repeat the macro for all the 7 charts on each sheet and all 12 worksheets for each month of the year. I am very new to VBA and coding of any type and have looked but can't find the solution.
The macro I am currently using for "Chart1" on "Jan graphs" is below. The charts are labelled Chart1, Chart2, Chart3 etc.
Sub ClearLabels()
Worksheets("Jan graphs").ChartObjects("Chart 1").Chart.ApplyDataLabels _
Type:=xlShowLabelAndPercent
For Each x In Worksheets("Jan graphs").ChartObjects("Chart 1"). _
Chart.SeriesCollection(1).Points
View 8 Replies
View Related
Mar 12, 2008
I'm trying to format the color of the data series in a graph that was produced from a pivot table. The pivot table contains a count of each traffic light in each project phase. eg.
. Traffic Light
Phase Amber Green Red Total
Initiate 1 3 2 |6
Design 5 3 |8
Implement 3 5 |8
---------------------------------------------
Total 4 13 5 |24
From this I have created a bar graph. (graph does not represent above data). Now, I'm trying to get each data series to represent their own colour. Instead of the default formatting. Cause every time I click on refresh data on the pivot table, it resets it to default.
ActiveChart.SeriesCollection(1).Select
With Selection.Interior
.ColorIndex = 45
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(2).Select
With Selection.Interior
.ColorIndex = 43
.Pattern = xlSolid
End With........................
View 3 Replies
View Related
May 16, 2008
Background: I have a Chart sheet with a combination bar/line chart on it. Data is coming from a "datasheet". There is a combo box on the chart sheet that allows you to choose a specific office whose data you want to view. Pick an office from the dropdown and the chart changes. The data is a count of closed cases by month. The data sheet shows 22 Offices with their monthly data, along with entries for each month of the Upper Control Point, Lower Control Point and Mean based on the nationwide data for that month.
The chart itself uses bars to show the office's case count per month, and I've incorporated 3 additional data series to plot the UCP, LCP and Mean as lines for each month.
I'd like to change the color of the bars to be Blue, Green or Red depending on where they fall in the 3 lines. I sort of have it working, but I'm stuck.
The code below grabs the value of each series point and compares it to the 3 control points and does change the colors. I've figured out how to use the If, next to change from one series point to the next, but cannot figure out how to loop through each of the control points. As it stands now, it looks only at the first month's control point and uses that for each successive month.
I hope this is clear and I've tried to keep it as short as possible while still providing enough information.
Sub chartbarformat()
'
' chartbarformat Macro
' Macro recorded 5/15/2008 by vacoyoungd
'
Dim x As Integer
Dim val As Variant
Dim AveragePending As Range
Dim LowerPending As Range
Dim UpperPending As Range
View 8 Replies
View Related
Nov 1, 2011
I have a combination chart with data from Sun thru Sat (7 days). My manager wants to display only the weekday data (M,T,W,Th,F) and remove the weekend data (Sat,Sun). However, I also need this data to be included in other charts I need. I was thinking in years past, that I was able to remove specific data from the chart directly without having to alter or change the spreadsheet data? I have copied a second set of data and hid the weekend data, however, I have gaps in the data, which I need to remove in the chart display. Not sure what to do, or the best avenue?
View 2 Replies
View Related
