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
-
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)
English (US)
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)
-
Thank you! It worked perfectly! :)
Answers
-
=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")))
-
Thank you, however, I still cannot make it work :(
-
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 -
Thank you Marce! For some reason, I get every time the same result: Major Project.
I cannot see what's wrong :(
-
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!
-
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
-
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)
English (US)
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)
-
Thank you! It worked perfectly! :)
-
Happy to help!😁
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!