I wonder if you could help. Forgive me also if I posted this in the wrong category!!!
I am looking to creat a heat map for compliance. Sounds easy and in basic terms it is.
What I want is for the worst case scenario to displayed... let me explain!
I have a list of Buildings... lets say Building A, B, C
I have a list of compliance issues... lets say 1, 2, 3, 4, 5
Each building will have all 5 compliance elements pertaining to the building.
Easiest scenario first... if all 5 elements are compliant, ie GREEN, then the building will be coloured GREEN.
If any element is out of compliance, therefore RED, I want the building colour to be RED.
I appreciate that the individual compliance elements will need to be altered manually, probably through an excel sheet if I am honest, but is there any way to provide the function above in Visio?
I have done all sorts of search engine searches but without the correct terminology I have drawn a blank. I have tried Status maps, compliance map, heat map, SCADA etc...
I have attached an excel sheet which I have had to colour manually... hopefully it will better explain my point!
Any help or pointers gratefully received :o)
open shape sheet
user.comp1 = <1 or 0 for compliance item 1>
user.comp2=
user.comp3=
user.comp4=
user.comp5=
fillforegnd = if (user.comp1+user.comp2+user.comp3+user.comp4+user.comp5>0,RGB(255,0,0),RGB(0,255,0))
If you use RGB, you can actually give degrees of redness to the non compliance (or other colors like yellow).
If you want some sort of "5 rung ladder" bar....a bit more complicated, but basically this idea for 5 "rung" shapes grouped with the building
(math a bit more complicated....but play with it and you will get the idea).
user.comp cells can be props cells if you want to edit on main screen in vision
(Might want eventdblclk = docmd(1312) if you want to double click shape to update comps manually).
Thank you for your response. Please forgive my newness to Visio... but none of your response made any sense at all!
I am using Visio 2003 so maybe that is why?
Your addition of the v2003 is a necessary piece of the puzzle, in my response I would have just suggested using data graphics (assumes you are at least at v2007 pro or higher). Vojo's response means that you have to do a little shape engineering in the shapesheet which is explained over here...
http://msdn.microsoft.com/en-us/library/aa245244(v=office.10).aspx
and not for the usual newby....
al
Al is correct....takes some practice to do this....so
1. make a square
2. shape==>custom properties
3. name the first one testdata1 (make top 2 entries testdata1,....as you get better, you can mess with the other fields)
4. repeat for say 2 others...testdata2....testdata3
5. done/finish
6. right click the square
7. open shape sheet (notice a section 2 down called props or custom properites....your 3 testdata fields are there...specifically props.testdata1, props.testdata2, etc....dont touch them)
8. page down to fill section
9 in the cell called fillforegrnd, type = if(props.testdata1+props.testdata2+props.testdata3>0,RGB(255,0,0),RGB(0,255,0)
10. farther down is a section called events....in eventsdblclk type = docmd(1312)
11. done / finish
now double click square....your 3 props come up.....change testdata2 to a 2....done/finish....see the square turn red
repeat....testdata2 = 0....done/finish...see square turn green
study this...and you are on your way
From here, you can look at how you want testdatas populated from spreadsheet and such.....this takes even more effort....wizards and VBA
(I dont do any sort of automatic link to excell...I use visio strictly for drawing stuff....but I am sure Al and others can point on your way)
good luck
Hi
Can some one please advise on how to link excel data to visio shapes...as mentioned in the last post...
Basically i am trying to generate a heat map..
I have got a visio structure (very basic, just set of linked squares) i want to colour those boxes based on the status of items in those box in an excel file...
For example..
The box in visio file says X PLan
In the excel file the Status of the X Plan is submitted
I want to have the box coloured as Yellow
Now the status of X plan in Approved
I want the colour of the box to be Green
Note: I am using visio 2007
Standard or Professional Version?
Because linking shapes (ShapeData) to Excel needs Professional version.
Otherwise you have to code it yourself with VBA.
I am using Visio 2007 standard...
Can i please get some help with coding to link shape data in visio to excel.
I am a novice coder and would seriously have no idea on how to do it in visio.
Thanks