Assign Value to Words

[curious723]

Greetings,
I want to know how to assign a value to a word. Currently I have a drop down list that has the following info:

Complete-Green
In progress-Yellow
Not Started-Grey
At Risk- Red
I want to have a value associated with the items in the list, but I dont want the value shown in the cell.

Complete-Green- 3
In Progress- Yellow- 2
Not Started-Grey -2
At Risk- Red- -3

I know how to write the conditional statement for the colors however not sure how to place the value with out replacing the text with the number.

thanks in advance for your help.
Curious

 

[VoG]

You will need code to do this Where is the cell containig the data validation abd where should the result go?

 

[curious723]

The information in the drop down boxes are in Cloumns E-L rows 8-41.
I want the information in the Check point Cell to sum the total of the rows.

 

[VoG]

Start here http://www.contextures.com/xlFunctions02.html

 

[curious723]

thanks I will check this out.

 

[MarkCBB]

is it not posible to use a vlookup and the OR function in the CF? and then add helper columns to find the value of the text with vlookup.