Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
New to Formulas  Need help with Text Answers
I have tried to create a formula using three options being
1. Does not meet Insurance Standards,
2. Meet Insurance Standards
3. Exceeds Insurance Standards
for a particular series of questions.
If you choose "Does not Meet Insurance Standards" then then next column Score will also populate 1 and the cell will highlight yellow. If you enter "Meets Insurance Standards" then the Score will autopopulate 2 and the cell will highlight green. If you enter "Exceeds Insurance Standards" the Score cell will auto populate 3 and the cell will highlight darker green.
Please help someone
Mytch
Comments

Sorry I should have said that the Score cell will be coloured in relation to the numeric value ie 1 = Yellow, 2 = Light Green and 3 = Darker Green
Hope this makes sense
Mytch

Mytch,
A couple different ways to do this, depending on what you need and like.
Let's start with the easy part:Once you have that number in the Score column, go to the Conditional Formatting icon on the tool bar
and add three rules there.
http://help.smartsheet.com/customer/portal/articles/516359conditionalformatting
You could use either the Score or Standards column as your criteria.
But let's assume that the conditional formatting is not the source of your problem.
A few questions I would ask:
1. Are you using the Score for anything other than color?
If not, you can have the conditional formatting directly on the Standards column.
I suspect it is used in weighting of some fashion  but wanted to point that out  and provide answers.
2. Are you OK with the number being in the Standard selection?
That is,1. Does not meet Insurance Standards
2. Meet Insurance Standards
3. Exceeds Insurance Standards
vs this
Does not meet Insurance Standards
Meet Insurance Standards
Exceeds Insurance Standards
as the options.
The formula for that would be:
=VALUE(LEFT([Standards]23, 1))
for row 23
 this just takes the first character and turns it into a number
(again, I'm assuming the number goes into a calc somewhere.)
Assuming you like the other (nonnumber options) then
=IF(Standards23 = "Does not meet Insurance Standards", 1, IF(Standards23 = "Meet Insurance Standards", 2, IF(Standards23 = "Exceeds Insurance Standards", 3, 0)))
for row 23
works, but is kind of ugly because the text is so long.
Since the three texts start with different letters, though ....
=IF(LEFT(Standards23, 1) = "D", 1, IF(LEFT(Standards23, 1) = "M", 2, IF(LEFT(Standards23, 1) = "E", 3, 0)))
for row 23
The full text is probably easier to read.
If the choices are more varied, then I might be tempted to use the FIND() function
but one must be careful as "Meet" shows up in "Meet" and "Does not meet" (Find is not casesenstitive)
I hope this helps.
Craig

Thanks Craig for you advice. It will give it a try.
Mytch