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

Best Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp Community Champion
    edited 02/02/25 Answer ✓

    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
    Audry89 ✭✭✭
    Answer ✓

    Thank you! It worked perfectly! :)

Answers

  • VBAGuru
    VBAGuru ✭✭✭
    edited 01/30/25

    =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
    Audry89 ✭✭✭

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

  • Marcela
    Marcela Employee

    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!

    Need more information? 👀 | Help and Learning Center
    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Audry89
    Audry89 ✭✭✭

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

    I cannot see what's wrong :(

  • Audry89
    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
    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
    jmyzk_cloudsmart_jp Community Champion
    edited 02/02/25 Answer ✓

    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
    Audry89 ✭✭✭
    Answer ✓

    Thank you! It worked perfectly! :)

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp Community Champion

    Happy to help!😁

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!