Color Pivot Chart Series Dependant On Series Name

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


ADVERTISEMENT

Use One Series To Color Weight Another In Pivot Chart

Jan 19, 2013

I have a data set that I'm trying to present in a pivot chart and am wanting to use one series to color code or weight another series in the chart. For instance, let's say I have different product categories and two metrics for each, a profit margin (expressed as a %) and a revenue figure (expressed as $s). I want the height of individual bars to represent the profit margin but have the level of opaqueness represent the revenue figures...ie the more revenue associated with a product the more opaque it becomes.

View 6 Replies View Related

Color Bar Chart Series Based On Series Name

May 15, 2008

I'm using the following code to set the colours of the slices of all the pie charts in my spreadsheet based on each slice's legend's label: ....

View 9 Replies View Related

Format Color Of Pivot Chart Data Series

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

200 Series But Get (The Maximum Number Of Series Per Chart Is 255) Error Message

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

Line Chart - Remove Series Code But What If No Series Exists?

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

Show Chart Data Series Labels On All Series. 2007

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

Spurious XY Scatter Chart Series Adding Series

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

Chart - Second Series Starts Where First Series Ends?

Sep 21, 2011

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.

Excel version: Excel 2010
OS: Windows 7

View 5 Replies View Related

Pivot Chart - Displaying Two Series

Nov 2, 2012

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.

View 2 Replies View Related

Coloring Pie Chart Using Series Collection Only Returns One Color?

Jul 22, 2014

I am trying to change the color of piechart using the code below:

Code:
Sub ColorPieChart()
Dim i As Integer
Dim myCol(10) As Long

[Code].....

Basically, I want each series in my graph to have the colors formulated by myCol. The code above, however, only colors the graph with color given my myCol(1), so it seems as if something is wrong with my loop.

View 8 Replies View Related

Change Color Of Chart Series Based On Source Value

Apr 23, 2008

it is possible to have the series of a stacked graph to change color based on the source value.

e.g.
i have a stacked cone graph made up of 5 piece (series)
i need each piece colored either green, yellow or red depending on whether its value is large than another

View 5 Replies View Related

Excel 2007 :: Changing Bar Series Color To Adjacent Cell Font Color

Aug 8, 2012

I am trying to use VBA in Excel 2007 to change bar chart series colors. I have found a few posts that link it to a cell background, but I'm struggling to find one that does the font color.

It would be great if I could change the bar chart series to match the color of the text in the A column, so that if I highlighted the value in A1 and changed the text color to orange for whatever reason, the chart updates the value of 1.2 to an orange bar (see below).

T. A1 B1

Sample 1 1.2

Sample 2 2.1

Sample 3 1.7

Sample 4 5.6

View 3 Replies View Related

Summing Series Of Maximums In Larger Series

Aug 22, 2014

I consider myself decent at Excel, but I don't have quite the depth of knowledge I would like. Here is my issue:

I currently have a series of 50 numbers for each row on a 300 row spreadsheet. I would like to write a formula that identifies the six largest numbers in that series and adds them together. So, for example, if the series was 8-6-4-4-4-5-1-1-0-15-3-9, the formula would spit out 47 (15+9+8+6+5+4)

How do I write that kind of formula?

View 8 Replies View Related

Chart Making- Chart With 3 Data Series

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

Change Chart Series Pasted From Another Chart

Jun 19, 2008

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.

View 3 Replies View Related

Use VBA Variable In Chart Series

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

Adding A 2nd And 3rd Series Onto A Chart

Jan 24, 2007

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.

View 9 Replies View Related

Dynamically Chart Series

Sep 18, 2008

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.

View 9 Replies View Related

Each Chart Source Row As A New Series

Dec 13, 2006

my problem is:

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...

View 9 Replies View Related

Scale Chart Series To MIN & MAX

Sep 26, 2007

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.

View 9 Replies View Related

Reference Last Chart Series

Jan 3, 2008

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.

ActiveChart.SeriesCollection(SeriesCollection.Count)

instead of

ActiveChart.SeriesCollection(1)

would work collect the last series added, which is what I want, but when I apply this I get an run-time '424' error.

View 2 Replies View Related

Name Each Name Series Collection Of Chart

Feb 7, 2008

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 ....

View 9 Replies View Related

Series Fill Color Method

Sep 17, 2009

Using XL07 I'm trying to programmatically set the fill type to solid and the colour to a colorindex constant for a series in a chart.

I can select/activate specific charts and series, but can't work out how to modify the properties of the series.

I can't for the life of me piece together the method, properties and syntax for doing this.

View 2 Replies View Related

Adding And Removing Series To Chart?

Jun 6, 2014

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?

View 5 Replies View Related

Determine Selected Series On Chart?

Mar 26, 2014

Is there a way of determining the series that has been selected on a chart?

View 3 Replies View Related

Updating Multiple Series' On Chart

Nov 18, 2008

I have a chart with 3 series':

Series1
Series2
Series3

The chart is called chtEngagement. I want to use vb to update these series'.
Series1 should be shown as:

View 5 Replies View Related

Change Colour Of Chart Series

Jan 4, 2010

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.

View 5 Replies View Related

Set XValues For Series Class (Chart)

Feb 5, 2010

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.

View 2 Replies View Related

Making Chart Series Dynamic?

Jun 6, 2012

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.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved