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

SmartSheet Newbie

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.

ker9

@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-Stearn

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".

ker9

@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-Stearn

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".