Excel Form with Traffic Light Result

[CBlackmo]

I have 7 questions with a list of selection results for each question (1 answer per question)that correspond to a number of "points". I want the user to select the answer from a list and then have the answer populate a field with the point associated with the answer. (example: Question 1 is AGE: If person is 30-36, point value is 3, if person is 37-43, point value is 2, if person is 44-51, point value is 1, etc.) Then based on the total points from the 7 questions, I want to show a different colors for the grand total of points(example: 0-4 = Proceed, 5-9 = Caution, 10 or above = Stop)

 

[SkipVought]

Hi,

Check out Format > Conditional Formatting

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[CBlackmo]

I'm not sure conditional formatting will help. The table listed below is in a drop down box so the user selects one of the age groups. Each selection returns a value (the # listed beside the age). Thanks for your help.

age
30-36 3
37-43 2
44-51 1
52-63 0
64-68 1
69-72 2
73-78 3
79-81 4
82-87 5
>= 88 6

 

[GlennUK]

You said

I want to show a different colors for the grand total of points

and I'd say that Conditional Formatting is perfect for that. Why are you not sure about it?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[CBlackmo]

Each question's point value is added together for a grand total. The grand total is the red, yellow, green. My question is how can I take the answer from the list box selection and assign the point value?

Age List box: Totals for each question
30-36 Total point value for Age = 3
37-43
44-51
etc.

creatinine List Box: Total point value for Creatinine=1
.6 or less
.7-.8
.0-1.2

 

[GlennUK]

If you have a table like this:

age score
30-36 3
37-43 2
44-51 1
52-63 0
64-68 1
69-72 2
73-78 3
79-81 4
82-87 5
>= 88 6

as the source for a drop-down, then use INDEX(MATCH to find the score for the chosen item, like:

=INDEX(score_list_ref,MATCH(age_chosen,age_list_ref,0))

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[DPlank]

Or apply an IF function to the list of ages like:

=IF(AgeValue = "30-36", 3, IF(AgeValue = "37-43", 2, IF....etc

Once this is applied in the relevant cells and you have the points, apply Conditional Formatting as Skip suggested to change the cell colour as you choose.

Cheers,
Dave

"Yes, I'll stop finding bugs in the software - as soon as you stop writing bugs into the software." <-- Me

For all your testing needs: Forum1393

 

[CBlackmo]

GlennUK, thanks for the formula. It worked but only the first 3 drop-down list selections. When you select the 4th list option, the formula goes to N/A.

 

[ck1999]

Did you make sure the fourth list option is identical to your table

ck1999

 

[GlennUK]

You should have a 2 column table for use with the lookup, and be using column 1 of that table as input for the Data Validation. That way there should never be any mismatch between the drop-down and the formula.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[CBlackmo]

All, thanks so much for your assistance. I finally got the formula to work! Have a great day