Databars length on one key color on another

[fiammdin]

Hi there,

im confused with Excel at the Moment.


im trying to get databars which length are based on the value within the cell but the Color is depending from another keyfigure.

Any idea on how to solve that?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Achivement[/TD]
[TD]Status[/TD]
[TD][/TD]
[TD]Status bar[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]G[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Here the first databar should be 10 Long and red, the second should be 45 Long but green and the third should be 80 but yellow.

thx a lot to all.

cheers

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

Excel does not do what you want directly. However, we can be devious.

Use four rules and not just one. The first rule will fill the whole cell with red if the value in column B is "R". It will fill the whole cell with green if the value in column B is "G" and, you guessed, it will fill the whole cell with yellow if the value in column B is "Y".

That is the first three rules and if you apply them D2 will be all red, D3 will be all green and D4 will be all yellow.

Now we can apply a DataBar.
Set the value of the cell in column D to be 100-"the value in A".
Now create a DataBar that is white and works from Right to Left.
Final touch, check "Show Bar Only" to hide the numbers.


Regards,

 

[fiammdin]

Thats so smart
I will try it asap, but it sounds so logic that i hate myself to not have thought about it.

Thx a lot

 

[fiammdin]

Works perfect.
thx a lot RichXL.

 

[RickXL]

You're welcome.

Sometimes, when you can't take No for an answer, you just have to outsmart it.


Regards,