Automating bubble chart colours

Does anyone know if the colour of a bubble within a chart can be 
conditionally formatted through reference to a cell within the spreadsheet ?

I want the colour of each bubble within the chart to based on the result of 
a calculation within the spreadsheet. Currently the cells containing the 
calculation are conditionally formatted to give a red, amber, green result 
and I want to link the colour of the conditionally formatted cells to the 
colour of the associate bubble.
(At the moment I update the bubbles manually) 

0
Vdubboy (2)
6/9/2006 11:31:03 AM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
445 Views

Similar Articles

[PageSpeed] 20

Hi,

The normal work around for making a charts formatting conditional is to 
use multiple series. Then via formula only plot those points 
(bubble/columns/markers/bars) that are valid.

See Jon's page for examples.
http://peltiertech.com/Excel/Charts/ConditionalChart1.html

Cheers
Andy

Vdubboy wrote:
> Does anyone know if the colour of a bubble within a chart can be 
> conditionally formatted through reference to a cell within the spreadsheet ?
> 
> I want the colour of each bubble within the chart to based on the result of 
> a calculation within the spreadsheet. Currently the cells containing the 
> calculation are conditionally formatted to give a red, amber, green result 
> and I want to link the colour of the conditionally formatted cells to the 
> colour of the associate bubble.
> (At the moment I update the bubbles manually) 
> 

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
6/9/2006 11:45:48 AM
That's perfect, thank you Andy

"Andy Pope" wrote:

> Hi,
> 
> The normal work around for making a charts formatting conditional is to 
> use multiple series. Then via formula only plot those points 
> (bubble/columns/markers/bars) that are valid.
> 
> See Jon's page for examples.
> http://peltiertech.com/Excel/Charts/ConditionalChart1.html
> 
> Cheers
> Andy
> 
> Vdubboy wrote:
> > Does anyone know if the colour of a bubble within a chart can be 
> > conditionally formatted through reference to a cell within the spreadsheet ?
> > 
> > I want the colour of each bubble within the chart to based on the result of 
> > a calculation within the spreadsheet. Currently the cells containing the 
> > calculation are conditionally formatted to give a red, amber, green result 
> > and I want to link the colour of the conditionally formatted cells to the 
> > colour of the associate bubble.
> > (At the moment I update the bubbles manually) 
> > 
> 
> -- 
> 
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
> 
0
Vdubboy (2)
6/9/2006 3:03:02 PM
Reply:

Similar Artilces:

Gradients in Column Chart
I can't see a way to choose my own gradients in an Excel 2007 column chart. Am I missing something or is that functionality gone? Try the following:- 1. Click on one of the columns. This will highlight all of them. 2. Right click / Format Data Series (window of same name should launch) / Fill - in here there is an option called Gradient fill. Is this what you want? If my comments have helped please hit Yes. Thanks. "Erin Dicks" wrote: > I can't see a way to choose my own gradients in an Excel 2007 column chart. > Am I missing somet...

CRM and Fax automation question
Hi, I'm looking for a good or better fax server that can be easily and seamlessly integrate with MS CRM 3.0 or 4.0. I'm thinking of RightFAX or GFIFax... or even if I install these fax server in my environment, do I still need to do programming and customizations, such as post callout when a new Fax activity is created? Please advise :) You immediate response is appreciated. "Benjamin Fallar III" <fallar@pldtdsl.net> wrote in message news:3484C69D-B193-4B48-86A4-A4529D9DE4C6@microsoft.com... > I'm thinking of RightFAX or GFIFax... or even if I install ...

Charts #15
I know how to produce group and a stacked bar graphs, but I do not know how to combine the two designs into one graph. For example, Item A and B on the x axis each have 4 bars grouped together. Now, I'd like to have additional information stacked withing each of these 4 bars. Any insight would be greatly appreciated! -- mariab ------------------------------------------------------------------------ mariab's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31118 View this thread: http://www.excelforum.com/showthread.php?threadid=507850 Here are some lin...

Automation
Hello Everyone, Recently our IT group installed service pack 3 on our computers. I wrote some code in word that automatically attaches it self to an outlook email and sends it out. I'de like to disable two windows that i didn't get before they installed service pack 3 there are not titles so here is a description of the boxes: 1 - A program is trying to access email addresses you have store in outlook. do you want to allow this? then it asks for "allow access for" (1,5,10 minutes) 2 - then i get a similar one that just asks you if you're sure you want to send ...

Oranizational chart
Hello. I've created an organization chart. The problem is that when I enter much text in a box, the text disappears (when I edit again the box, the text remaining no longer exists). Do anyone know how to make it allow more text in the boxes? Thanks in advance. ...

Help with colour formula
In column W row 2 I have a formula eg: =IF(AND(L2<=5,V2<=7.1),V2,"") copied down to row 1001 not every row has a result , lots of rows are blank . The numbers in column W need to be in red font L V W 2 6.6 22.4 3 0.4 18.3 4 2.0 9.7 5 6.8 0.2 6 2.6 5.1 5.1 7 -0.5 16.3 8 0.2 -4.5 -4.5 and so on Can some one help me with this please? -- bill gras Hi, Because your formula is returning a Null string for false these cells will appear empty so w...

Excel Charts #20
I have workbook with different worksheet in it. each tab has a chart and a data table. i would like to create one more tab and have the master chart and underneath all baby charts where the information is being combined into master chart. Please I need help to show me a step by step directions how would i do this. I have created all charts including master now I need to put them all on one sheet. You can e-mail me at nk1519@aol.com Thank you, nk ...

how to switch the primary and secondary Y axis to opposite chart s
Using Excel 2007, evertyime I refresh pivot table data, my charts lose almost all of the formatting (ex. axis will disappear and chart type will revert from lines to columns). Most annoying problem I have when this happens is the primary Y axis moves from the left side to the right side of the chart, and when I re-introduce the secondary Y axis, it will appear on the left. How can I easily move the primary Y axis back to the left side of the chart or switch the two axes? I've tried choosing Layout - Axes to hide and re-display the axes but this did not work. Format the primary cate...

Provisioning/Automation tools for Exchange??
I have been asked to find some tools possible of automoating mailbox moves to specific databases/storage groups based on the department of a user for instance. Let's say User1 is part of HR and has transferred to Marketing. All the HR mailboxes reside on SG1 DB1 and all Marketing reside on SG1 DB2. Is there any software anyone knows of that can read the department attribute from AD and then move the mailbox according to defined rules you put in place. Any and all help appreciated! Mike In a couple of situations, organizations I have worked in have talked about having something li...

chart #4
All, With VBA I'm making a chart sheet. This chart sheet is than viewed by the user. I have protected this sheet, so he can make no changes. I have also set some settings for the graph options: Application.ShowChartTipNames = False Application.ShowChartTipValues = False Now I notice that you can still select the different parts of the graph. Is it possible to switch this off? I also would like to add a button on this chart sheet. I tried this with: ActiveSheet.Buttons.Add(869189.25, 125211.75, 395520.75, 289965).Select ActiveChart.Shapes("Button 1").Select ...

Is it Possible to Automate a Graph?
I have a sheet that has data in weekly fashion in the first and second rows. (Row 1, week: Row 2, data.) Is it possible to have a graph populate off of the information in rows 1 and 2 that automatically includes data for new weeks as they are entered in to the sheet? For example, I want to create a graph for weeks 1 through 3 now, and want the chart to automatically update if I add data for week 4. Row 1 Week 1 2 3 Data 12.5 17 18 Thanks for the assist! -Adam Use a defined name using the offset function for your series --...

Help setting scale limits on chart
I have a chart based on data in an xls sheet. Both the x and y data data change when calculations are made. So I don't want the x axis variable to be fixed, per se, but I'd like the scale limit dialog to allow me to put in the cell on the sheet to set the lower and upper limit. So when I place the cell address into the dialog for min and max I get an error I tried both the address (i.e. N2, or like this =N2) both were errored out. Is there no way to do this?? I guess I need to build a macro instead that always selects the same cells and have the macro built the chart that way? I did...

Chart size relative to contents
2 questions: 1.) I have a chart which plots circles in an X Y scatter. Based on the size of the circles within the chart, the circles appear as an ellipse. I can resize the chart manually to bring the circles back to shape - I was wondering if there was some quick VBA to make the chart area relative to the X and Y axis length. 2.) I use these charts as a template, so the data changes. If I know the extreme X and Y values, is there a method to control the axis length with that information - while still taking advantage of the "automatic" scaling feature? (this will be i...

Way to automate adding pictures in a series in Excel?
I'm putting a column of pictures of people next to a column with their names. If I have the pictures together in a folder, with the photo file names numbered with the corresponding row numbers that the pictures go in, is there a way to record a macro so that (for instance) 28.jpg will go into the picture column in row #28, then 29.jpg will go into the picture column in row #29, etc. In other words, insert a whole series of pictures (filenames the same as their appropriate row numbers) in their correct places, by initiating one macro? If anyone has any idea of how that could be done, I wou...

Display percent and value on stacked column chart
I'm displaying cumulative $ totals from 3 sales groups on a 100% stacked column chart. We've recently exceeded 100% of our total $ goal so the chart isn't able to display the amount above 100%. If I switch to a standard stacked column chart, I can't choose the display percentage option. I downloaded the Rob Bovey's Chart Labeler but I can't get it to display what I want. Perfect world would be displying the dollar value of each sales group in the column segments and showing percent of total goal on the y-axis. I would even be okay with showing the dollar valu...

Automating transfers, is this possible?
Hi All. I am using MM 2007 Premium and I'm using Advanced Bills. In "Help" it says .... "Make electronic transfers. Set up automated transfers between any accounts you track in Money" I have a set up an "xfer" from my checking to my savings (same bank) to occur once a month but Money doesn't actually do anything unless I manually go to "Bills Summary", check it and and click "Pay Online". This isn't what I would call an automated transfer. Is there any way to actually make this happen? Thanks, Richard ----== Posted via ...

VBA automating Word from PowerPoint 11-22-09
I have an add-in macro that exports PPT content to a new Word document for each PPT file in the same folder as the currently open PPT file. I've noticed that when the macro is finished, and I go to Word, Word seems "paralyzed" (I can't use any menu commands or close the doc). If I Alt+Tab back to PowerPoint, and then Alt+Tab back to Word, all is well. Is there something about automating Word from PowerPoint that needs to be "cleared" when the PowerPoint macro ends - some way to release PowerPoint's control over Word? I found http://www.pptfaq.com...

Chart Question #5
I have 5 points (labeled 1 thru 5) that are randomly generated each with an x,y coordinates. I want to plot them on a scatter diagram and then ID each point by its label not its coordinates. Can somebody help. Hi, Download the free add in at http://www.appspro.com/Utilities/ChartLabeler.htm this is designed just for this purpose. -- Thanks, Shane Devenshire "Xl2007" wrote: > I have 5 points (labeled 1 thru 5) that are randomly generated each with an > x,y coordinates. I want to plot them on a scatter diagram and then ID each > point by its label not its coordinat...

Interactive Chart?
Hello, I need to create an interactive Excel Line Graph. What I need to do is be able to click on a datapoint on the graph an have it take me to the data specific to that point. For instance if I have a chart that shows how many phone calls ar taken in a week I want to be able to click on a datapoint specific t the day and be taken to the information for that day. Any Advice? SQL FR -- squishyfro ----------------------------------------------------------------------- squishyfrog's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3268 View this thread: http://w...

Working With Charts
I have created "Cash Withdrawal" as a payee to account for ATM cash withdrawals from my checking account. I am able to call up a report about this "payee" in the Reports Gallery. But I am unable to customize the report and make it a chart. How can I make a line chart of my cash withdrawals? Line Chart is not an option for the Payee Spending report. You can, however, use the Account transactions report to create a line chart and only the select the one payee. "David Lingenfelter" <anonymous@discussions.microsoft.com> wrote in message news:06d401c39d0d...

Scattered XY Charts and labeling the points
I have a scattered chart with a name column and another two columns with the data to plot the charts. I am unable to mark those points with the corresponding names in the name column. Is there a way to do this? Thanks ~ Lori Lori - This capability isn't built in to Excel, but there are a couple free add-ins you can download to enable you to use a range of cells to label a series of points: Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials a...

automation
Hi there; I am copying specific cells from a file (actuallu use the address approach, like aa!c12) to another file. The cells address in source file are always the same, but in target file changes each time to lower rows than previous one. I have 100 source files at least, how to automate this, instead of clicking at each cell each time between source and target files. Thanks for your help. in your target file, use dir to get the first source file, copy the cell(s) desired, close this source file, use dir() to get 2nd file and repeat till finish "Darius" wrote: > Hi there; ...

Excel 2007 Chart Colors
I posted this a few days ago and no one has responded. Does anyone know how to manipulate the colors of a chart in Excel 2007??? I have the following code that was written in Excel 2003. Every time I run it in Excel 2007 it crashes. I can't find anything about changing the colors of a series in an Excel 2007 chart. Also, when I try to record a macro to change the chart colors to gain insight, nothing gets written to the macro regarding the color change. Help! Sheets("Chart1").SeriesCollection(1).Interior.ColorIndex = 5 You received two replies. It appears...

stop chart series from updating
I have created a chart and now want to change the data it uses as the source, BUT I do not want my original chart to update as I want to create a different chart. Does anyone know to freeze the chart so it is no longer linked to the cells? One way would be to take a picture of the chart as it is now. Click the chart; hold Shift key down; use menu item Edit and select Copy Picture; use As displayed; then use Paste command to paste picture on worksheet or in a Word Doc, etc. Another way is to delink the chart. Click of a data series in the chart, look in Formula Bar and you see somethin...

Help with charts.
I'm creating a mail merge in Word, using a large Excel data source. The file contains multiple names, with various associated data. I want to create an individualized chart for each name, using data associated with their name in the Excel data source file. One customized chart per name, merged into the Word document. Can this be done. I cannot figure out how to merge data into a chart.--- or is there a better way? Please help if you can.. In article <5A0A45FF-B5F8-44DC-9924-A7ACCA0ED26E@microsoft.com>, HRassist@discussions.microsoft.com says... > I'm creating a mai...