Return cell value if all criteria is met

Miss_Priss

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))))))

Jason Albrecht

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, ""))))

Jason Albrecht

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?

Miss_Priss

@Jason Albrecht

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.

Jason Albrecht

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, ""))))

Miss_Priss

@Jason Albrecht Thanks for cracking this for me! It's still a struggle transitioning my thinking from excel to Smartsheet.

Jason Albrecht

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.