And Is Blank; And Not Is Blank
Here is what I want to accomplish: If the Employee Number is Blank and the Assignment Role is Not Blank, put in "AP". If the Employee Number is Not Blank, and If the Assignment Role is C, L, or S Adjunct, put in "PT", any other item in the drop down for Assignment Role should be "FT", as long as the Employee Number is Not Blank. If Employee Number is Not Blank and Assignment Role is Blank, leave blank.
IF(AND(NOT(ISBLANK([Assignment Role]@row, [Employee ID]@row, "AP", IF([Assignment
Role]@row = "C Adjunct", "PT", IF([Assignment
Role]@row = "L Adjunct", "PT", IF([Assignment
Role]@row = "S Adjunct", "PT", IF([Assignment
Role]@row = "" ", "", FT"))))
I can get parts of it to work, but cannot get the IS NOT BLANK or IS BLANK to work properly. Thanks for any help.
Best Answer
-
It's easier to break the IF statement apart then put it together.
Try this.
=IF(AND([Employee ID]@row = "", [Assignment Role]@row <> ""), "AP", IF(AND([Employee ID]@row <> "", OR([Assignment Role]@row = "C Adjunct", [Assignment Role]@row = "L Adjunct", [Assignment Role]@row = "S Adjunct")), "PT", IF(AND([Employee ID]@row <> "", OR([Assignment Role]@row <> "C Adjunct", [Assignment Role]@row <> "L Adjunct", Assignment Role@row <> "S Adjunct")), "FT", IF(AND([Employee ID]@row <> "", [Assignment Role]@row = ""), ""))))
Answers
-
It's easier to break the IF statement apart then put it together.
Try this.
=IF(AND([Employee ID]@row = "", [Assignment Role]@row <> ""), "AP", IF(AND([Employee ID]@row <> "", OR([Assignment Role]@row = "C Adjunct", [Assignment Role]@row = "L Adjunct", [Assignment Role]@row = "S Adjunct")), "PT", IF(AND([Employee ID]@row <> "", OR([Assignment Role]@row <> "C Adjunct", [Assignment Role]@row <> "L Adjunct", Assignment Role@row <> "S Adjunct")), "FT", IF(AND([Employee ID]@row <> "", [Assignment Role]@row = ""), ""))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!