Indicate when Departments are Different

Is there a formula to indicate when data in 2 columns are not empty and not equal? For instance, I want to know that a case is Interdisciplinary (using a checkbox or other mechanism in a separate column) when PI Department (selected from a drop down) is not in the same department as Co-PI Dept (selected from a drop down), Co-PI 2 Department (selected from a drop down), OR Co-PI 3 Department (selected from a drop down). What I have currently isn't working properly- it will indicate when the Department is the same.
Current formula (doesn't include Co-PI 3 Dept, but needs to) is: =IF(OR(NOT(ISBLANK([Co-PI Dept]@row)), NOT(ISBLANK([Co-PI 2 Dept]@row))), IF(OR([PI Department]@row <> [Co-PI Dept]@row, [PI Department]@row <> [Co-PI 2 Dept]@row), 1, 0), 0)
Best Answer
-
Hello @fryeaf
Try this:
=IF(OR(AND([PI Department]@row <> [Co-PI Department]@row, [Co-PI Department]@row <> ""), AND([PI Department]@row <> [Co-PI 2 Department]@row, [Co-PI 2 Department]@row <> ""), AND([PI Department]@row <> [Co-PI 3 Department]@row, [Co-PI 3 Department]@row <> "")), 1, 0)
Melissa Yamada
melissa@insightfulsheets.com
Data made simple, spreadsheets reimagined
Answers
-
Hello @fryeaf
Try this:
=IF(OR(AND([PI Department]@row <> [Co-PI Department]@row, [Co-PI Department]@row <> ""), AND([PI Department]@row <> [Co-PI 2 Department]@row, [Co-PI 2 Department]@row <> ""), AND([PI Department]@row <> [Co-PI 3 Department]@row, [Co-PI 3 Department]@row <> "")), 1, 0)
Melissa Yamada
melissa@insightfulsheets.com
Data made simple, spreadsheets reimagined -
Hi @Melissa Yamada, it looks like that is working!!!
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!