using NOT

michellelkirkland

Hello,

I am trying to write a formula to identify open deviations that are in another sheet. The column that I am referencing {Deviation Tracker Range 3} has multiple options for the stage it is in but I only want to collect what is in {Deviation Tracker Range 1} if the cell does not contain "Closed". (for reference {Deviation Range 2} is looking at the parent batch number}

=JOIN(COLLECT({Deviation Tracker Range 1}, {Deviation Tracker Range 2}, {Deviation Tracker Range 3}, if(not "closed"), contains([parent batch]@row, @cell), " *and* ")

Darren Mullen

sss @michellelkirkland A couple issues with the formula that jump out.

You have the Tracker Range 3 right after the Tracker Range 2, that is not the correct syntax. You need to use the criterion for each range right after the range.

Also for the not function you would just say:

not(@cell = "closed")

Darren Mullen

sss @michellelkirkland A couple issues with the formula that jump out.

You have the Tracker Range 3 right after the Tracker Range 2, that is not the correct syntax. You need to use the criterion for each range right after the range.

Also for the not function you would just say:

not(@cell = "closed")