Using NOT HAS with AND in formula

Kate Folwell

I am trying to capture work done that was assigned to individuals in three different scenarios:

Scenario 1) work done by members of a specific team (team members listed on a different sheet, noted as {ID Team Roll Call Sheet Range 1})

Scenario 2) work done by people that are NOT members of this specific ID Team

Scenario 3) and collaborative work done by people on this specific ID Team AND not on this specific team.

In all three scenarios I am trying to trigger a separate column checkbox (Harvey balls used elsewhere).

For Scenario 1 : I trigger a checkbox that compares the multi-select "Assigned to" contact cell to a range on another sheet using this formula which works:

=IF(HAS({ID Team Roll Call Sheet Range 1}, [Assigned To]@row), 1, 0)

For scenario 2: I trigger a checkbox that compares the multi-select "Assigned to" contact cell to a range on another sheet using this formula which works to an extent:

=IF(NOT(HAS({ID Team Roll Call Sheet Range 1}, [Assigned To]@row)), 1, 0)

Right now it returns a checkmark as soon as a contact is found that is not in the specified ID Team list, and also even when there are both ID team members and those that are not ID team members. Not what I was going for. I tried CONTAINS instead of HAS and that checked everything, also not working.

For Scenarios 3: I tried to trigger a checkbox that pulled both above conditions together- when both ID Team members AND non-ID Team members were listed The formula version below is not coming up with an error message(which is a sort of success?), but is not checking any boxes (used as column formula).

=IF(AND(HAS({ID Team Roll Call Sheet Range 1}, [Assigned To]@row), NOT(HAS({ID Team Roll Call Sheet Range 1}, [Assigned To]@row))), 1, 0)

Any suggestions on corrections or other solutions here are welcome!

Thanks,

Kate

Kate Folwell

Hi, Genevieve-

Thanks for weighing in on this topic. As I wanted to set this plan up for projects with different members and non-members, I wanted to stay with the list/formula idea. And yes, the numbers of members were around 12 or so, with continual changes- so the idea of listing out in the formula was giving me pause.

Having said that, setting up a contact list for the assigned to column and a cell change value automation for the checkbox column would be a way easier way of skinning this grape. I did end up getting a formula that works for all three scenarios by taking advantage of the way SS actually uses HAS in this scenario- not as logic or the intent of HAS with a multi-select would dictate.

Again, thanks for your insight- I will use that at some point!

Genevieve P.

Hi @Kate Folwell

Since you're comparing a multi-select cell against a list in another sheet, the formula can only compare the entire contents of the cell to the list (versus parsing out individual contacts).

This is why you're seeing a checkbox if the cell only contains members on the list, because the members in the cell match the members in the list. However, as soon as you have a mix of contacts, this list of users from the cell no longer matches any combination of users in the referenced list.

This means you can create two comparisons: either everyone in the Assigned To cell is in the Team List, or there is at least one contact who is not on the list.

If you need to parse out individual contacts, then you would either need to have multiple single-select Contact columns set up in this sheet, or we could identify each person from the team within the formula itself (instead of referencing a table).

How many contacts are in the other sheet, would it be possible to list them out in the formula?

Cheers,

Genevieve

Kate Folwell

Hi, Genevieve-

Thanks for weighing in on this topic. As I wanted to set this plan up for projects with different members and non-members, I wanted to stay with the list/formula idea. And yes, the numbers of members were around 12 or so, with continual changes- so the idea of listing out in the formula was giving me pause.

Having said that, setting up a contact list for the assigned to column and a cell change value automation for the checkbox column would be a way easier way of skinning this grape. I did end up getting a formula that works for all three scenarios by taking advantage of the way SS actually uses HAS in this scenario- not as logic or the intent of HAS with a multi-select would dictate.

Again, thanks for your insight- I will use that at some point!

Genevieve P.

I'm glad you were able to figure something out!