Need help writing a formula

In a column titled Status, I have one status as Active 1x and the other status as Active 2x. I created a new column to show values either 1 or 2 in a cell depending on the status. I need a formula that shows - if the status is Active 1x, then the cell should show '1', and if the status is Active 2x, the cell should show '2'.
Best Answers
-
@Ken Armstrong The formula didn't work. I'm posting a screenshot to show my status column and the column that says test formula is where I need the values (1 or 2)
@Mike TV Here is the screen shot
-
@Dheeraj.c16 Try this now that I can see this snap I can correct:
=IFERROR(IF([Status*]@row = "Active (1x)", 1, IF([Status*]@row = "Active (2x)", 2)),"")
Let me know if that worked
Ken Armstrong
Smartsheet Project Manager, GE Aerospace
Certified Smartsheet Administrator
Be Firm! Be Fair! Be Friendly! Be Honest!!!
-
@Dheeraj.c16 Please make sure you accept my answer so other community members can see that this is resolved.
You are very welcome and feel free to reach out if you need anything else.
Ken Armstrong
Smartsheet Project Manager, GE Aerospace
Certified Smartsheet Administrator
Be Firm! Be Fair! Be Friendly! Be Honest!!!
Answers
-
Could you please provide a screenshot of your sheet so we know what 1x and 2x mean specifically? It could mean a few different things and I'm not sure if you really mean the cell has "1x" in it or something else.
-
=IF([Status]@row = "Active 1x", 1, IF([Status]@row = "Active 2x", 2)). This should work and you can set this as a column formula and let it run.
Ken Armstrong
Smartsheet Project Manager, GE Aerospace
Certified Smartsheet Administrator
Be Firm! Be Fair! Be Friendly! Be Honest!!!
-
If you want you can add an IFERROR statement so any blank cells will show blank and not no match. Here it is:
=IFERROR(IF([Status]@row = "Active 1x", 1, IF([Status]@row = "Active 2x", 2)),"")
Ken Armstrong
Smartsheet Project Manager, GE Aerospace
Certified Smartsheet Administrator
Be Firm! Be Fair! Be Friendly! Be Honest!!!
-
@Ken Armstrong The formula didn't work. I'm posting a screenshot to show my status column and the column that says test formula is where I need the values (1 or 2)
@Mike TV Here is the screen shot
-
@Dheeraj.c16 Try this now that I can see this snap I can correct:
=IFERROR(IF([Status*]@row = "Active (1x)", 1, IF([Status*]@row = "Active (2x)", 2)),"")
Let me know if that worked
Ken Armstrong
Smartsheet Project Manager, GE Aerospace
Certified Smartsheet Administrator
Be Firm! Be Fair! Be Friendly! Be Honest!!!
-
@Ken Armstrong It worked. Thank you so much
-
@Dheeraj.c16 Please make sure you accept my answer so other community members can see that this is resolved.
You are very welcome and feel free to reach out if you need anything else.
Ken Armstrong
Smartsheet Project Manager, GE Aerospace
Certified Smartsheet Administrator
Be Firm! Be Fair! Be Friendly! Be Honest!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.8K Get Help
- 474 Global Discussions
- 205 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!