Having data create colored fields

[writeguy37]

I have been tasked with trying to computerize a Daily Observation Report for a 911 center. Users will answer Evaluation questions with a score between 1-7 or with N.O. for "not observed" or N.R.T. for "not responding to training". What I would like is that, depending upon the value placed in the answer, a color will appear on another sheet in a single cell---green for values 4 or greater, red for values less than 4, yellow for NO, and black for NRT. Each set of questions would be answered daily, and the colors would appear on a date-separated form on another sheet which would allow for a quick, visual representation of a trainee's progress over the course of successive days or weeks. There would be approx 24 different questions for the trainer to answer. There would be nothing other than the color "response" on the other page---no additional data. The resulting page would just be multiple colored cells that show improvement or lack thereof as well as sections that have not been observed (N.O.).

It would also be great if, when hovering over the blank field, a tool tips type of box could appear that would contain the scoring standards for each respective question. These standards are question specific and differ from one to another (don't know if this is possible btw).

I know this is asking a lot, so any help or direction would be appreciated.

[dilipandey]

Hi writeguy37,

what is the excel version you are using.. i.e., Excel 2003, 2007, 2010 etc?
Can you upload a sample workbook ? Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[writeguy37]

I'm using 2007 and 2010. I tried to find a sample online but couldn't.

I'm on an iPhone at the moment, so I will do my best to explain. On the "tracker" sheet, there will be verticle columns of colored cells (1 Row for each of the 20+ questions). Each column should be defined by the date of the evaluation. So, at the conclusion of the Work week, there would be 5 columns of 20+ rows. The colors for each cell within this section would be either green, red, yellow, or black depending upon the value used on the previous sheet for the corresponding question. So, it could be something like this:

3/1 3/2 3/2 3/4
Appearance. G. G. G. G
Knowledge. Y. G. G. Y


With the actual colors appearing in place of the G or Y

Hope this helps....tough to do on the iPhone.

[writeguy37]

The dates should, obviously, appear ABOVE the appropriate color.

[shon]

Capture.PNG

just to ensure this is what you want..please confirm? If the attached is okay then should be pretty easy with conditional formatting.

The dates should, obviously, appear ABOVE the appropriate color.

[writeguy37]

Yup. That is pretty much exactly what I am looking for. I still need to do the question sheet as well, but the picture is spot on.

[writeguy37]

So is this an easy format to create?