IF AND Formula for 5 Options
Hey Community, please help me figure this out.
We have 5 options for types of projects and they are the following:
Technical Services Projects
Technical Services Programs
Enterprise Projects
Enterprise Programs
ODE
We are trying to output one of those five options based on the columns Project Type and Program Type.
Project type can be either Technical Services Projects or Enterprise Projects, while Program Type can be either Technical Services Programs or Enterprise Programs. The last option (assuming this would be the false part of the IF statement) is ODE.
See picture below to get an idea of the columns, we are trying to output it in the OPPTY Type Master column (ignore the red row).
Any help on this is greatly appreciated!!
Best Answer
-
So if I understand correctly, they select one of two values from "Project Type" column, OR they select one of two values from "Program Type" column, and you want whichever one thing they've chosen to be displayed in "OPPTY Type Master" column, unless they've chosen nothing from either column, in which case the "OPPTY Type Master" value should be ODE. Correct?
In your OPPTY Type Master column, try the following:
=IF(ISTEXT([Project Type]@row), [Project Type]@row, IF(ISTEXT([Program Type]@row), [Program Type]@row, "ODE"))
In English, if there's a text value in Project Type, show that text here; otherwise, if there's a text value in Program Type, show that text here; otherwise (no text in either column,) show "ODE" here.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
So if I understand correctly, they select one of two values from "Project Type" column, OR they select one of two values from "Program Type" column, and you want whichever one thing they've chosen to be displayed in "OPPTY Type Master" column, unless they've chosen nothing from either column, in which case the "OPPTY Type Master" value should be ODE. Correct?
In your OPPTY Type Master column, try the following:
=IF(ISTEXT([Project Type]@row), [Project Type]@row, IF(ISTEXT([Program Type]@row), [Program Type]@row, "ODE"))
In English, if there's a text value in Project Type, show that text here; otherwise, if there's a text value in Program Type, show that text here; otherwise (no text in either column,) show "ODE" here.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!