How to automate a column based on data from other columns?

Hello! I need your support to automate the results shown in a column. I need some formula, or condition, or automation I can use….

I have data in 2 columns (Impact and Resources, picture below) that can be changed from a drop-down menu. Based on the combination of answers from both columns, I need the column "Task Type" to automatically change to any of the 4 answers of its drop-down menu. So far, I haven't been able to successfully do it, can someone help me? How can I do it?

The results that I expect are these, based on the combinations from Impact and Resources:

Impact in either 0, 1, or 2 with Resources in 0, 1, or 2 should show Fill-in

Impact in either 0, 1, or 2 with Resources in 3, 4, or 5 should show Time Waster.

Impact in either 3, 4 or 5 with Resources in either 0, 1, 2 should show Quick Win.

Impact in either 3, 4 or 5 with Resources in either 3, 4 or 5 should show Major Project.

As a bonus, if you can tell me how to generate a chart in Smartsheet based on this data in the 4 quadrants, that would be fantastic.

Thank you for the support!

Best,

Audry

Find more posts tagged with

Accepted answers

jmyzk_cloudsmart_jp

Hi @Audry89

While testing, I found that using =[Symbol] to convert a symbol column value into text returns different "number words" depending on the language setting.

Español (Costa Rica)

https://app.smartsheet.com/b/publish?EQBCT=cacfbfeb22ec4aa7baa9196f1388a1c4

English (US)

https://app.smartsheet.com/b/publish?EQBCT=bf205eeedfcb451a861ea405c71c47cd

To work around this, I added helper columns—Impact Number and Resources Number—and used the following formulas to convert symbol values to numbers. This ensures consistency and easy manipulation across different language settings. (Tested in the US English environment.)

[Impact Number] = IF(Impact@row = "Empty", 0, IF(Impact@row = "One", 1, IF(Impact@row = "Two", 2, IF(Impact@row = "Three", 3, IF(Impact@row = "Four", 4, IF(Impact@row = "Five", 5))))))
[Resources Number] =IF(Resources@row = "Empty", 0, IF(Resources@row = "One", 1, IF(Resources@row = "Two", 2, IF(Resources@row = "Three", 3, IF(Resources@row = "Four", 4, IF(Impact@row = "Five", 5))))))

Once converted to numbers, you can use them to determine the Task Type:

=IF(AND([Impact Number]@row <= 2, [Resources Number]@row <= 2), "Fill-In",
   IF(AND([Impact Number]@row <= 2, [Resources Number]@row >= 3), "Time Waster",
   IF(AND([Impact Number]@row >= 3, [Resources Number]@row <= 2), "Quick Win",
   "Major Project")))

Some language settings (e.g., Japanese) return numbers instead of words for symbols. Considering that Smartsheet is often used in a multi-lingual environment, I think assigning numerical values to symbols makes life simple for everybody.

日本語 (as shown below, the formula returns a number except for empty)

https://app.smartsheet.com/b/publish?EQBCT=980c9328df2542318475e36f2b83bd73

Audry89

Thank you! It worked perfectly! :)

All comments

VBAGuru

=if(and(Impact<3,Resourses<3),"Fill-In",If(and(Impact<3,Resources>2),"Time Wasters",if(and(Impact>2,Resources<3),"Quick Win","Major Project")))

Audry89

Thank you, however, I still cannot make it work :(

Marcela

Hello @Audry89

To add to the comment above, I just want to remind you that Smartsheet functions are caps-sensitive.

Please try this:

=IF(AND([Impact]@row < 3, [Resources]@row < 3), "Fill-In", IF(AND([Impact]@row < 3, [Resources]@row > 2), "Time Wasters", IF(AND([Impact]@row > 2, [Resources]@row < 3), "Quick Win", "Major Project")))

The formula evaluated the values in the Impact and Resources columns and returns ¨Fill-in¨, ¨Time Wasters¨, ¨Quick Win¨, or ¨Major Project¨ based on the specified conditions.

Hope this helps.

Marce!

Audry89

Thank you Marce! For some reason, I get every time the same result: Major Project.

I cannot see what's wrong :(

Audry89

I think the issue is that I'm using symbols to represent Impact and Resources. Is there any way that we can include symbols in the formulas? How should I include them?

Thank you very much!

Audry89

Can someone tell me if it's possible to create this automation based on the symbols in the columns, as I have it?

I'll appreciate your support :)

Thanks!

Audry

jmyzk_cloudsmart_jp

Hi @Audry89

While testing, I found that using =[Symbol] to convert a symbol column value into text returns different "number words" depending on the language setting.

Español (Costa Rica)

https://app.smartsheet.com/b/publish?EQBCT=cacfbfeb22ec4aa7baa9196f1388a1c4

English (US)

https://app.smartsheet.com/b/publish?EQBCT=bf205eeedfcb451a861ea405c71c47cd

[Impact Number] = IF(Impact@row = "Empty", 0, IF(Impact@row = "One", 1, IF(Impact@row = "Two", 2, IF(Impact@row = "Three", 3, IF(Impact@row = "Four", 4, IF(Impact@row = "Five", 5))))))
[Resources Number] =IF(Resources@row = "Empty", 0, IF(Resources@row = "One", 1, IF(Resources@row = "Two", 2, IF(Resources@row = "Three", 3, IF(Resources@row = "Four", 4, IF(Impact@row = "Five", 5))))))

Once converted to numbers, you can use them to determine the Task Type:

=IF(AND([Impact Number]@row <= 2, [Resources Number]@row <= 2), "Fill-In",
   IF(AND([Impact Number]@row <= 2, [Resources Number]@row >= 3), "Time Waster",
   IF(AND([Impact Number]@row >= 3, [Resources Number]@row <= 2), "Quick Win",
   "Major Project")))

日本語 (as shown below, the formula returns a number except for empty)

https://app.smartsheet.com/b/publish?EQBCT=980c9328df2542318475e36f2b83bd73

Audry89

Thank you! It worked perfectly! :)

jmyzk_cloudsmart_jp

Happy to help!😁

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.