I recently got a request from a subscriber that asked if I could help them create a cylinder chart. However the sample that was received looked just like an Excel Bar Chart so I went with that as my solution.
Here is Surajit’s request:
“I want to create a stacked cylinder chart to display sequence of different products in a pipeline. Here both product and quantities are dynamic. I want excel to change color depending on product. Kindly help. – Surajit”
Here are the Challenge Requirements :
1) The Pipeline data can grow or shrink (i.e. sometimes the pipeline may have less products or more products all of varying quantities).
2) Solution must be automated (i.e. Manually changing the colors of each data point is not acceptable).
3) Pipeline segments must be representative of the quantity (i.e. a segment with the quantity of 100 should be twice the size of a segment with the quantity of 50).
4) Products may be enetered in differnet stages of the bar chart, but should have the same color. (i.e. Any segment that is for SKO01 should be yellow – Note: setting any series to a specific color is okay as a manual step, but you should only have to do it one time).
5) The client only needs to enter the Product and Usage Value for each segment. (i.e. After being setup all the user has to do is to is enter the segment product and quantity value).
6) Final chart should look similar to the client sample. (i.e. I will accept better representations of the data (so please send those too) but the client really wanted it to look like they way they wanted it to look (i.e. as a bar/cylinder type chart, not a line, pie chart, etc.)
7) VBA is okay to use, however, the client would prefer no-VBA (i.e. In case they need to change something when you are not available. BUT seeing a VBA solution would be way cool!)
8) I reserve the right to be wrong and update any of the challenge requirements if someone makes a better chart solution than I did 🙂 (i.e. this is a joke, but if someone points out an issue in the requirements (and please do if they are not completely clear), I may have to update them)
Here is the Challenge Sample Data File: Sample-Pipeline-Usage-Chart-Challenge.xlsx
I think this is a tough one (you may not think so, so I hope to learn from you as well). It will certainly test you and it will take me awhile to write it up. Therefore, I will not post the solution until Thursday of next week to give us both time 🙂
GOOD LUCK! And thanks for playing the Friday Excel Challenge Game.