IF and HAS and OR formula combined (or alternative)

Morgan Frost

Formula wizards! Can you help me with this one?

I have a column for Primary Team (single select) and a column for Secondary Teams (multi-select). I want to drive a report widget to include rows where a certain team, let's say "Team X", is either primary or secondary. Report parameters won't let me do this (because I also need to filter the report to those with Status "active" and the reportbuilder will let me use OR or AND but not both). Technically I need ([Primary Team] OR [Secondary Team]) AND "Active") in the report for it to work there.

Thus the workaround...I think the best way would be to have a "Team X" column (checkbox) and a formula that looks for the name in the team columns and checks the box if it is found.

If you think there is a better way I'm all ears.

What would the formula be? I get lost with IFs and ORs and combos.

Logic is:

If [Primary Team] is "Team X"

OR

If [Secondary Team] HAS "Team X"

then "1" (for the checkbox)

Thank you!

Genevieve P.

Hi @Morgan Frost

The helper checkbox column sounds like a great idea! Since only the Secondary Team is multi-select, you only need to use the HAS for that column. Here's how I would build that formula:

=IF(OR([Primary Team]@row = "Team X", HAS([Secondary Team]@row, "Team X")), 1, 0)

Here are some Help Articles I used to build this: IF function / OR function / HAS function / @row function

Let me know if you have any questions about this.

Cheers,

Genevieve

Paul Newcome

Here's a little bit of info based on my own personal experience with this very some situation:

Using a checkbox type for the helper column means you would need additional checkbox columns (and more similar formulas) for each team you want to be able to specify, but...

If you use a text/number type as your helper column, you can use something like

=JOIN([Primary Team]@row:[Secondary Team]@row, " / ")

or if they are not side by side

=[Primary Team]@row + " / " + [Secondary Team]@row

To pull them both into the same cell. Then in your report builder you can point at this single helper column and tell it to pull rows that contain "Team X".

I personally have found that this is much easier (for me at least) when creating reports and metric sheets because I can set reports to look for a specific name in the same column which makes "Save as New" pretty quick when updating the report builder if you need the same report for different teams, and I only need one range for formulas containing cross sheet references.

I can tell the formula to pull rows that contain a name in a single column (and use cell references for easy drag-filling) instead of having to update each formula with a new cross sheet reference for each team's checkbox column.

Morgan Frost

Thank you, @Paul Newcome and @GENEVIEVE LARAMEE

I will write down both of these for future reference and I will utilize Paul's @row reference to keep the whole thing simple/easier to maintain. That's always my goal, and usually more challenging!

Paul Newcome

Happy to help. 👍️

Sometimes simplifying a process can be a bit of a challenge in and of itself. Especially when you need to keep in mind additional factors such as reports and/or metrics sheets. Definitely feel free to come back to the Community if you have any more questions or would like any more help or input.