A challenge: formatting in a chart

[livelaugh12]

Hello all!

If you like a challenge, this post is for you! I am a new member and can't wait to hear what you guys propose to help me with my problem! I will try to be as clear as possible. Here it is....

The task: Make a tower that illustrates the composition of our current pool of donations. Donations are made in rounds so each level of the tower is one round. Also make it so the user can plug in numbers and the chart adjusts accordingly. Easy enough! Just make a 100% stacked column! Not so fast... The original donor (the company) tends to keep donating throughout the many rounds of donations and makes up a certain percentage of each round after round 1. I would like to illustrate this percentage in each respective level. EXAMPLE: In a 100% stacked column chart, the Company donates $100 in the opening round. Of the second round, which happens to be $200, the company is resposible for 50%. The chart would then show a first level of one color (red) and a second level (twice as thick) with the right half red and left half a different color (green) that would represent the other donations.

The problem: I initially thought the original company would only make up the rest of the rounds in incrememnts of 10%. I make the chart I wanted by making a 100% stacked column with 10 columns, then adjusting the format of the columns so there is no space between them. This makes for the appearance of one big tower. I then went ahead and changed the formats individualls so the series that came up when I plugged in 10% had one column that was red and the rest green, 20% and I would reformat 2 of the sections red etc.... Little did I know, the company sometimes makes up a percentage that can be as specific as 22.5%! No more 10 columns. It doesn't get more specific than .5%, so I now am stuck with a 100% stacked column chart with 200 column and up to 7 levels. This means I would have to individually format almost 9000 pieces of this chart!!!

My (semi)solution: I already know that there is no way to format multiple data series in a chart. All I could really think of to save some time was to make a macro that filled a data series, but even with this i had to press the same key hundreds of times. Is there a way to create a macro that would re-do this initial macro over and over like 20 times? This would at least save me some time as I could do 10% at a time as opposed to .5%.

Here is the code from my macro. All it does is change the fill of a selected data series.

Sub FillSeriesMacro()
'
' FillSeriesMacro Macro
'
' Keyboard Shortcut: Ctrl+p
'
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(166, 226, 239)
        .Transparency = 0
        .Solid
    End With
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.SeriesCollection(3).Points(199).Select
End Sub

All help and suggestions are appreciated!!! Thank you in advance.

 

[Rukt]

livelaugh12,

I've mocked up an alternative scheme, along the following lines:

• Set up a table showing the "Company" and "Other" donations in each round, along with per-round and running totals (all formatted as Currency)
• Create a 100% stacked area chart, with values from Company and Other, and Cumulative on the category axis
• Change the horizontal axis type from Automatic (Text) to Date, so that it's scaled properly
• ** Apply some data selection trickery to give instantaneous changes between rounds **
• Use error bars to simulate gridlines
• Rotate all labels 90°
• User the "camera tool" to create an image of the chart, and rotate that -90°

I've provided a link to my example (you'll need to download it, since the effect isn't shown properly in the web version). If this looks like it'll be a workable method for you, I'd be happy to explain in more detail.

https://www.dropbox.com/s/k3fc89ix1vedlp6/donations - vertical area chart.xlsx

Cheers,
Rukt