Changing colours based on Excel Data

[GOOMM]

Ladies and Gents,

The challenge has been put to me to develop a visio datashape that will
change colour based on values from and excel spreadsheet.

The issue I am faced with is that the colour is dependent on the number
value. The next issue is that colour / number value differs from shape to
shape.

For example:

Shape A 1 to 5 (Green) / 6 to 10 (Yellow) and 11+ (Red)
Shape B 1 to 3 (Green) / 4 to 6 (Yellow) and 6+ (Red)
Shape C 1 to 10 (Green) / 11 to 20 (Yellow) and (21+)

This means as the spreadsheet changes based on daily rates the colour would
vary also.

Day 1 - a = 4 (Green) b = 3 (Green) and c = 11 (Yellow)

On day two the values would change and now indicate the following:

Day 2 - a = 6 (Yellow) b = 7 (Red) and c = 9 (Green).

Any solutions or starting points would be appreciated.

Thank you.

 

[AlEdlund]

Although you mentioned the fact that you had different shapes with different
ranges, you didn't mention how many range sets you might have (5, 10,
more?).
v2007 supports datarecordsets from excel (you can put the excel data into
the shapes)
v2007 supports datagraphics you can apply different datagraphics to
different shapes
v2007 supports the use of color by value (with ranges) in the datagraphics
http://office.microsoft.com/en-us/visio/FX100649211033.aspx?CTT=96&Origin=CL100636311033
al

 

[AlEdlund]

apologies, I should have added Visio 2007 professional,
al

 

[John... Visio MVP]

Ladies and Gents,

The challenge has been put to me to develop a visio datashape that will
change colour based on values from and excel spreadsheet.

The issue I am faced with is that the colour is dependent on the number
value. The next issue is that colour / number value differs from shape to
shape.

For example:

Shape A 1 to 5 (Green) / 6 to 10 (Yellow) and 11+ (Red)
Shape B 1 to 3 (Green) / 4 to 6 (Yellow) and 6+ (Red)
Shape C 1 to 10 (Green) / 11 to 20 (Yellow) and (21+)

This means as the spreadsheet changes based on daily rates the colour
would
vary also.

Day 1 - a = 4 (Green) b = 3 (Green) and c = 11 (Yellow)

On day two the values would change and now indicate the following:

Day 2 - a = 6 (Yellow) b = 7 (Red) and c = 9 (Green).

Any solutions or starting points would be appreciated.

Thank you.

Create a shape with three custom properties (LowerLimit, UpperLimit and
Value)
In the Shapesheet, set the shape's Fill Cell to
=IF(Prop.Value>Prop.LowerLimit,IF(Prop.Value>Prop.UpperLimit,2,5),3)

For Shape A set LowerLimit to 5 and UpperLimit to 10, Shape B (3,6) Shape C
(10,20)

John... Visio MVP