#### 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

edited 12/09/19

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.

Mytch

Tags:

• edited 07/09/16

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.

Once you have that number in the Score column, go to the Conditional Formatting icon on the tool bar

http://help.smartsheet.com/customer/portal/articles/516359-conditional-formatting

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 (non-number 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.

=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 case-senstitive)

I hope this helps.

Craig

• edited 07/09/16

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

Mytch

This discussion has been closed.