I am trying to create a chart that will allow the user to add/remove series to compare with each other. When adding a series, Excel gives it a name (e.g Series1) the macro is then set to rename it to a particular cell, however the name Excel gives the series varies depending on the order in which the series are added.
I'm looking for a way to name lines in a pivot chart instead of default numbering eg ActiveChart.SeriesCollection(1). This in order to attach variable lines to y-axis no 2 no matter which selection is made in the pivot.
I'm having a problem with named ranges in a chart. I'm using Excel 2007 in Windows XP.
I have a line graph with twenty different series all using dynamic ranges. I created each series individually, typing the name and then the dynamic range I had previously defined. As far as I am aware, Excel demands when writing the reference that you specify which sheet/workbook this named range is from, so, given the scopes of my ranges are all 'workbook', I added the name of the workbook to the references.
e.g.
Series name: Series1
Series values: ='workbook name'!range1
This seems to work fine, but when I save and then re-open the file, the dynamic ranges no longer work. If I go to Select Data and look at the reference for a series, it has been changed thus:
Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.
In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter
=--(AND($AX8>$AA$4,$Y8>0))
and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.
Cell $AA$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.
Here's the first hard part:
For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.
Here's the 2nd hard part:
Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.
The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.
Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.
I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.
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:
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.
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?
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
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...........................
I would like to have two series of data using the same X axis (date, formatted in months). The Y axis is in intervals of 100,000.
The first data series is historical (actual) data (i.e. Jan 2009 to August 2011). The second data series is forecast (Sept 2011 - August 2012). So i want the forecast series to start immediately after the historical series. It is a 'line with markers' chart. The key objective is that the forecast data looks visually distinct from the historial series.
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.
I want to use a macro to change attributes of a series in a chart. Unbelievably, if the series has been pasted into the chart from another chart, and although the macro can address the series and even return values (eg name) correctly, the selection simply cycles to one of the original series on the chart.
I have the following code which puts a series onto a chart
With Charts("Test Chart") .SetSourceData Source:=Sheets("Data").Range("A1:B" & Nreadings), PlotBy:=xlColumns .Axes(xlCategory).MinimumScale = 1 .Axes(xlCategory).MaximumScale = Nteeth .Axes(xlValue).MinimumScale = Miny .Axes(xlValue).MaximumScale = Maxy End With
I would like to add a 2nd or third series onto the same chart using the same scale etc. How do I do this in VBA code? Whenever I add another line to add a series it just overwrites the first series I have shown here.
I have calculated data (the column and row headings are calculated as well) similar to this for which I need to create an automatically updating chart.
******** language="JavaScript" ************************************************************************>Microsoft Excel - test.xlsm___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1= ABCDEF1 BalloonsFlagsCushionsPillowsStickers2Blue136013Red352104Green703245Yellow075056Orange558327White26073Sheet2 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The chart should update if the data is filtered; so if a user doesn't want to see the 'orange' row they can filter the data accordingly. There is a finite number of rows and columns in the range but sometimes there will be less data. I have tried to convert the data to a table and that works quite well except the legend keys persist: so if there is no data for pillows and stickers the legends still appear and have to be removed manually. Also, there seems to be a bug in Excel 2007 whereby the removal of a chart linked to table data causes a persistent error message to appear. I know that I need to created a named range for the data series using OFFSET, and I have also read that I need to refresh this formula using VBA but I am now very confused and none of my attempts quite work.
I have a whole list of data which changes according to the users preferences It comprises of a NAME, X Value, and Y Value and the addition of X and Y. (these all have auto filters on them)
The user inputs under these fields, as many rows as they wish (which will be around 20ish)
I want it so when i click a button "CREATE GRAPH"
it selects the highest 10 values (if there is 10 values, if not then all the values) from the 4th column (X+Y) and then creates a graph. i want each row to be a new series...
I have a Scatter chart that plots a simple XYScatter plot. The data for this plot can be very wide ranging, and for that reason, I use AutoScaling to scale the axes. Nine times out of ten, it works just fine, but it seems that when the X data ranges from about 5200 to 7200 +/-, the autoscaling does not calculate well (example attached).
Autoscale range will go from 0 to 8000, leaving a large area of unplotted graph space.
I thought the original issue was that I may be plotting zeros or blanks, but I've eliminated that problem. I have yet to find another range of values that causes it, but I'm sure there's some. What is it about the way that Excel calculates its AutoScaling that I'm missing?
What do you all recommend to eliminate this problem? Manually scaling it is not really an option in this case, as I want this to be completely transparent to the user for ANY range of data.
I hope this passes muster. Anyways, I wrote some VBA code to handle an embedded chart in my workbook. I named the chart "Data" but i'm getting some unexpected trouble from VBA. Last time I worked on this set of code, it ran fine. When I try to run it now, it is throwing up an error: "1004 Unable to set name property of series class" The .name function is not working correctly but everything up till that point is working correctly. I have 5 sets of data in this chart also ....
I have two obscure Files and I want certain information from each file.
I started by importing the data on seperate worksheets.
and I wanted to make a table basically summerizing the info on a third worksheet
On a third Worksheet i wanted to reference from those files in a table as i mentioned. So i started referencing the cells that contain the data for the first line and second line and i wanted to fill the series so I wouldn't have to type the info into Excel (like =C8)
uhm to summerize the following::
the two files are a players file (which has there specific team and player name) and a stats file (which has there corresponding stats). So i have the following
Year Team League GP G A Pts PIM +/- 2011-12 Swift Current Broncos WHL 82 5 22 27 128 10
the cell references are
for the stats worksheet
C8, D8, E8, J9, I9 (GP,G,A,PIM,+/-) Pts is just an add
and the next line would be 25 plus the cell references (so like C33)
i would like to fill it all the way down the page for about 1000 players
but it didn't work for me - the next line was C12, C27 etc...
A common task for me is having to manually update the series formulas on charts to conform to some predefined conditions as the data changes. To reduce the strain of this, I've developed a VBA module that will read the various series A1-style formulas of the chart and update them according the the specific conditions. This works wonders on most charts, but I've suddenly run into a situation where Series.Formula property returns ONLY the R1C1 style. That is, where my series formula looks like this in the chart sheet formula bar: ...
I want to use 4 toggle buttons to be able to add and remove 4 different series to a chart. When the toggle button is clicked, a specific series is added to a chart, and when it is unclicked then the series would be removed.
The problem I am running into is that unless I add and remove each series in a specific order, I will get an error because I may be trying to remove a series whose series number is not on the series collection in the chart.
for example: if I remove series number 2 with the toggle button for that specific series, then the toggle button for series number 3 will remove the old series 4 (which is now 3), and the toggle button for series 4 won't work because series 4 will now have become series 3.
Therefore, would there be a way to remove a series based on the name of the series, rather than the series collection number?
I'm attempting to write a code that will change the colour of a series in a chart based on the name of each series. i.e. I have Roads, Water and Rail as three of my series names. can i somehow assign the colours yellow, blue and red to these series using macro code.
I'm getting an error when I try to set the XValues for a chart in my excel worksheet. The chart shows up fine and the new series adds fine though when I try to set the XValues the below error occurs
'Unable to set the Xvalues property of the Series Class' 1004. My Code is below. Can anyone see anything wrong with it? This could be a floor in my understanding of the chart object. I don't want to be selecting charts and working on an activechart because I'll have several in the one worksheet.
I am looking for a quick way to create a number charts using data from multiple sheets.
I have 12 sheets Apr - Mar that contain data in identical format. I also have sheets 12 sheets Apr Charts - Mar Charts, I need to create a number of charts on each sheet for the relevant month. To avoid having to create a ridiculous number of charts, I thought I would try to dynamically change the chart data series based on a value in say cell A1.
For example if cell A1 of the Apr Charts sheet = Apr, the chart will show the data for Apr.
The idea is that I can copy the Apr Charts sheet the required number of times and replace the value in A1, and my charts will update dynamically. I need to have seperate sheets for each of the monthly charts.
I have 2 columns of dates on which 2 separate events occur, e.g columns 1 is dates of receiving incoming calls (and there can be multiple incoming calls , so there are multiple rows with same date) and the other column is dates of outgoing calls (same deal of having multiple rows with same date, each row = 1 outgoing call). I want to plot both as line graphs and find out where they intersect , each one peaks and tapers.
How do I select all of the series on my chart and flip them over to the secondary axis? I can't seem to find an answer via google... Either VBA or straight Excel would be fine.
For the record, this is part of a larger question. I've been creating a chart using a couple for loops to work through a 2D array (VBA, clearly). At first I would just set each series to the second axis as it was created, but I've found that if I use collapseable groups to turn off different values...well, if I collapse the first series it all dissapears (still shows up in the legend, but the lines are gone.)
If I leave them on the primary axis I have no prolblem. So now I'm working on the theory that it's the way they are being added. So I'm looking for another way to put them on the other axis.
how do you put all series in a graph on the secondary axis all at once. Either an Excel based or VBA answer is fine (I can turn the Excel into a Macro if push comes to shove.)
I want to create 3 stacked bars on a chart, where each stacked par points to the same series data. This is for an automated reporting process and I don't want to create a 'staging' table if I don't need to. I have discovered that while the series formula can take an array in curly brackets of actual numeric values, if i fill that array with the same spreadsheet cell reference 3 times it is unable to evaluate it. By way of example:
1. This works =SERIES(Chart1_Data!$A$7,Chart1_Data!$B$1:$D$1,Chart1_Data!$B$7:$D$7,6)
2. This works =SERIES(Chart1_Data!$A$7,Chart1_Data!$B$1:$D$1,{592210.02,592210.02,592210.02},6)
3. This works =SERIES(Chart1_Data!$A$7,Chart1_Data!$B$1,'MI Excel Template v.1.xlsx'!testrange,6)
4. This doesn't =SERIES(Chart1_Data!$A$7,Chart1_Data!$B$1:$D$1,{Chart1_Data!$B$7,Chart1_Data!$B$7,Chart1_Data!$B$7},6)
