Return cell value if all criteria is met
Hello all!
After several hours and scouring the community archives, I am just not sure how to solve the last part of my formula.
The scenario is that I have a column with a formula to create "teams" based on what criteria is met in the rest of the sheet. Some lines on the sheet meet more than one criteria, but I've laid the formula out in a way that seems to be working for this purpose.
The only part that isn't working is the last bit of the formula below. Basically, the column I'm referencing includes a value that I would like to omit. If a cell in this column does not contain "Canada" then I want the formula to retrieve the value of the cell as a response.
Right now I'm getting an #INVALID COLUMN ERROR which I think is due to the column I'm referencing having the value I'm trying to avoid. Is this formula possible, or would I need a helper column to resolve this?
=IF([Project Activate]@row = 1, "Project Activate", IF(PSA@row = 1, "PSA Team", IF(GEO@row = "Canada", "Canada", IF(PSA@row = 0, [Project Activate]@row = 0, NOT(CONTAINS(GEO@row = "Canada", GEO@row))))))
Best Answer
-
Hi @Miss_Priss
I believe this is what you're after?
=IF([Project Activate]@row = 1, "Project Activate", IF(PSA@row = 1, "PSA Team", IF(GEO@row = "Canada", "Canada", IF(AND(PSA@row = 0, [Project Activate]@row = 0, GEO@row <> "Canada"), GEO@row, ""))))
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
Answers
-
Hi @Miss_Priss
If I'm reading the formula correctly, it looks like you've already accounted for "Canada" in a previous IF statement, suggesting that if all previous IF statements are true, the false statement can simply be GEO@row ?
Or have I misunderstood the outcome you're looking to achieve?
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
Yes I think we're on the same page. Basically the outcome would be to return all other fields in that cell but omitting "Canada" as a response. I know there's a conflict along my string somewhere I just can't quite get it right.
-
Hi @Miss_Priss
I believe this is what you're after?
=IF([Project Activate]@row = 1, "Project Activate", IF(PSA@row = 1, "PSA Team", IF(GEO@row = "Canada", "Canada", IF(AND(PSA@row = 0, [Project Activate]@row = 0, GEO@row <> "Canada"), GEO@row, ""))))
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
@Jason Albrecht Thanks for cracking this for me! It's still a struggle transitioning my thinking from excel to Smartsheet.
-
Glad I could help and thank you for the feedback.
From experience the transition gets easier over time.
Continue to reach out to community if ever in need.
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!