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

Mytch Jeffrey
edited 12/09/19 in Archived 2016 Posts

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



  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    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.



    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.

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


    I hope this helps.











  • Mytch Jeffrey
    edited 07/09/16

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



This discussion has been closed.