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.

Find more posts tagged with

Formulas

Accepted answers

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

All comments

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!)

=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")

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.