Conditional Formatting a Chart with data on the same bar!

[Nick.simpson19]

Hello,

I'm currently working on a project which I need some support with, I work in Lean Manufacturing and I need to automate a tool I have used for a long time. Its known as a line balancing chart (Yamazumi Chart).

At the moment I have to record the data on paper and manually draw the chart this hasn't been a problem up to now because I have only ever had to do it on one or two processes.

This time my team and I will be looking at approximately 200 processes so to try and speed things up I was hoping I could create something that would build the chart straight from the data being in putted. The problem I have is excel likes to colour certain series in certain colours.

I have attached a picture below of what Im trying to create however my processes can be up to 9 hours long and will have minute by minute data in putted. Any help would be kindly appreciated.

Capture.PNG

Regards

NIck

[Andy Pope]

You need vba code to format each series based on the status.

This example has a worksheet function to define chart, its location and the table of status.


If you also need to update the chart contents when new items are added you will need additional code to add extra series or remove existing ones.

[Nick.simpson19]

Thanks Andy,

My visual basic skills are non-exsistent (I must learn) but I have someone who is okish, if we get stuck ill post another message. Thank you so much.

Nick

[Nick.simpson19]

ANdy, Have tried to work with this but cant seem to get it to work. I would need to add approx 9 hours of data into this template and then it would build the chart on its own?

But i also need to be able to delete rows and then it will delete it out the chart at the moment it wont do this.

I wish i was better at excel.

[Nick.simpson19]

ANdy, Have tried to work with this but cant seem to get it to work. I would need to add approx 9 hours of data into this template and then it would build the chart on its own?

But i also need to be able to delete rows and then it will delete it out the chart at the moment it wont do this.

I wish i was better at excel.

[Andy Pope]

Not sure what you are having poblems with. All you need to do is extend the data series range and extend the range referenced by the YAMAZUMICHART function.

You will probably need to alter the Major Unit value on the scale to make it readable if you have 9 hours of tasks.

[Nick.simpson19]

Not sure what you are having poblems with. All you need to do is extend the data series range and extend the range referenced by the YAMAZUMICHART function.

You will probably need to alter the Major Unit value on the scale to make it readable if you have 9 hours of tasks.

Andy, this chart is testing me mate I thought I had cracked it last week so went out to do some observations on upon entering the data none of the chart colours would change correctly.

I have attached a copy of the obersvation, can you take a quick look and try and figure out what ive done wrong?

I am really really appreciative of your help, if I can get this working it will save my team approx 200 hours work. As we are going to enter the data straight into the excel sheet on the shop floor using tablets.

Hope you can help.

Kind Regards

A very confused

Nick Simpson

[Andy Pope]

You need to update the formula on Data sheet cell J2

=YAMAZUMICHART("Chart 1","Yamazumi's",E2:G110)