I need to change a column named Event Type to a "V" or "I" based on the value of another column.

I need to change the Event Type column to a "V" or "I" if a column named Event location contain the word "Virtual" or "virtual". I have tried several versions of IF statements but they all give errors. Below is the last version I tried.

=IF([Event Location]@row, "Virtual", "V", IF([Event Location]@row, "virtual"), "V", IF(NOT([Event Location]@row, "Virtual", "I", IF(NOT([Event Location]@row, "virtual", "I")))))

The Event location column will either be Virtual, virtual, or the name of the city where the event is located.

Tags:

Best Answers

  • ker9
    ker9 ✭✭✭✭✭✭
    Answer ✓

    @SmartSheet Newbie

    This should work regardless of capitalization (but may not catch spelling errors):

    =IF(CONTAINS("virtual", [Event Location]@row) = true, "V", "I")

    Hope this helps!

  • Monique_Odom_Comcast
    Monique_Odom_Comcast ✭✭✭✭✭✭
    Answer ✓

    Hello @SmartSheet Newbie, (Love the name!)

    Formulas aren't by nature case sensitive (unless you specify that information), so this can be simplified. An IF formula is basically set up as IF: this, then A, otherwise B - so we can also take out the last two statements. Try this:

    =IF([Event Location]@row = "virtual", "V", "I")

    If people have the ability to put spaces or other text, then you might want to consider adding a Contains layer:

    =IF(CONTAINS("virtual", [Event Location]@row), "V", "I")

    And last, if you don't want to rely on a formula, you could actually create an Automation to change the cell in the Event Type column. Your automation would look something like this: When a row is added or changed, where Event Location contains "virtual" (this would be a condition), change cell in Event Type to "V", otherwise change cell in Event Type to "I".

    If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!

    Monique Odom

    Business Process Excellence Manager

    Smartsheet Leader & Community Champion

    Pronouns: She/Her (What’s this?)

    “Take chances, make mistakes, get messy!” – Ms. Frizzle

Answers

  • ker9
    ker9 ✭✭✭✭✭✭
    Answer ✓

    @SmartSheet Newbie

    This should work regardless of capitalization (but may not catch spelling errors):

    =IF(CONTAINS("virtual", [Event Location]@row) = true, "V", "I")

    Hope this helps!

  • Monique_Odom_Comcast
    Monique_Odom_Comcast ✭✭✭✭✭✭
    Answer ✓

    Hello @SmartSheet Newbie, (Love the name!)

    Formulas aren't by nature case sensitive (unless you specify that information), so this can be simplified. An IF formula is basically set up as IF: this, then A, otherwise B - so we can also take out the last two statements. Try this:

    =IF([Event Location]@row = "virtual", "V", "I")

    If people have the ability to put spaces or other text, then you might want to consider adding a Contains layer:

    =IF(CONTAINS("virtual", [Event Location]@row), "V", "I")

    And last, if you don't want to rely on a formula, you could actually create an Automation to change the cell in the Event Type column. Your automation would look something like this: When a row is added or changed, where Event Location contains "virtual" (this would be a condition), change cell in Event Type to "V", otherwise change cell in Event Type to "I".

    If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!

    Monique Odom

    Business Process Excellence Manager

    Smartsheet Leader & Community Champion

    Pronouns: She/Her (What’s this?)

    “Take chances, make mistakes, get messy!” – Ms. Frizzle

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!