conditional formatting / traffic light chart question

Hey guys

pls see attached -

The values in B6:F7 of this tab pertain to the matrix in the Communications tab. B13:E18

I would like to highlight the traffic lights above depending on whether or not the results in D14 of the communications tab meet the benchmark set in E14:E17.

Meeting or exceeding b'mark would be green, 5% under Amber 6% under Red.

Ive seen Andy Popes traffic light chart sheet but its whoosh over my head!

Maybe this is too difficult in which case could someone recommend an alternative?

Cheers

Bob - thanks for your response.

I'm reading the link with interest! i'll not (if you dont mind "mark this post as solved") just in case i can't figure it out and have to ask something.

Hope thats acceptable.

Thanks alot

Hiya - as you have probably guessed, I have been tasked with creating a dashboard for the 10 department objectives (ive only posted the first obectiveve - communication).

Some objectives are measured monthly. Like membership stats and some - like the one i posted is measured every 6 months. I have NO clue how i'm going to toggle between years!! if you have the time/inclination and could recommend a way i'm ALL EARS!!!!

Thanks bery very much for knocking up a solution based on my posted sheet. I absolutly thrilled that its there is case i need it but ive just printed off sheets from your site and i reckon i'll try to crack it myself first!

My deadine for this section is 12 noon tomoorw so i've got time still.

Thanks alot Bob for your support.

Hi Bob - in your download XLD.Traffic Lights.XLS you have Xg Xr and Xy as named ranges P3 Q3 and R3. can i ask what these are for?

Additionally, it looks like i will need 5 separate threshold tables for each of the 5 q's in the trafficlight tab, is this right?

Cheers.

Darren

Hi Bob - Thanks very much for all your help, kind of you.

I surrender and if you could possibly attach the work book i'll be sure to study how you did it.

Thankls very much.

Hi Bob - terrific. I wont rest until i understand how you achieved this.

Please see the PM i sent you.

Thanks so much

Hi there - if any mods out there are wondering why this thread is now "unsolved" its because i have some further follow up questions. I hope that this is acceptable. Please pm me if not.

Hi Bob,

AS AND WHEN AND IF TIME PERMITS ---------------

The named rngs are a great idea, i really need to get into the habbit of this as it makes it easier for all to understand. So where poss i;ll use the named rng in the questions!

1) Re _MatchYearCol, I can see that this is matching _SelectedYear to _dataCommsYears and returs either a 1 or a 2. Why is this? is it related to column position? Therefore, next year when I add 2012 in col e and move benchmark along this would rtn 3?

2) In some cases there are up to 4 traffic light images over the CF'ed cells C8:C10 ect. Why is this?

3) should I wish to amend - either lower or higher the threshold it can be done in C8 by =AND(INDEX(_dataCommsResults,_matchQuestion1Row,_matchYearCol)<(INDEX(_dataCommsBenchmarks,_matchQuestion1Row))*95%,C9<>TRUE) changing the 95?

and the same in C9 =AND(INDEX(_dataCommsResults,_matchQuestion1Row,_matchYearCol)>=(INDEX(_dataCommsBenchmarks,_matchQuestion1Row))*95%, INDEX(_dataCommsResults,_matchQuestion1Row,_matchYearCol)<(INDEX(_dataCommsBenchmarks,_matchQuestion1Row)))

obviously i'd have to do F, I, L and O.

Lastly, but probably the most important. There has been an oversight on my part circa Q6 - Grapevine.

The full question is Q6) You usually hear important formal things through the grapevine or by rumour first.

In 2010, 59% of people said this was the case. So a benchmark was set to REDUCE this to 50%. Therefore, in 2011, this target has NOT been met! by 6%. I am really sorry about that.

Is there an easy way to adapt the formulea in O8:O10 to reflect this change. I tried messing around with the % and * operator but to no avail.

Otherwise I have more or less gained a grasp and recognised how you have "futureproofed" it.

Next pint of Black Sheep in Canary Wharf on me!