Check a Box based on Or/And

Sam McDonald

I have a checkbox I need to tick if a cell says ETM Contract Signed and/or Direct Contract Signed, or if the number of contracts is 0.

The below formula works when the column has either one of the two signed contracts but not when both are present. How can I re-work it?

=IF(OR([French Contract Status]@row = "ETM Contract Signed", OR([French Contract Status]@row = "Direct Contract Signed")), 1, IF([# ETM & Direct (FR) Contracts]@row = 0, 1))

Paul Newcome

You will need to add the rest of the statuses and update the column names using the below syntax…

=IF(OR([# Column]@row = 0, AND(OR(HAS([Status Column]@row, "ETM Contract Signed"), HAS([Status Column]@row, "Direct Contract Signed")), NOT(HAS([Status Column]@row, "1st Exclude Status")), NOT(HAS([Status Column]@row, "2nd Exclude Status")), NOT(HAS([Status Column]@row, "3rd Exclude Status")), ………………………………., NOT(HAS([Status Column]@row, "Final Exclude Status")))), 1)

Jeremy_D

If you change it to a CONTAINS function then it'll tick the box based on the presence of the ETM Contract Signed or Direct Contract Signed. Try this:

=IF(CONTAINS("ETM Contract Signed", [French Contract Status]@row), 1, IF(CONTAINS("Direct Contract Signed", [French Contract Status]@row), 1, IF([# ETM & Direct (FR) Contracts]@row = 0, 1, "")))

Sam McDonald

Hi Jeremy, this works great but is there a way to get it to tick when only one of those two options or both are present? There will be other times we have "Contract sent for Signing" or "Waiting on paperwork" in the same column so in that instance I don't want the box ticked. Only when it's either of the signed or both of them present.

Paul Newcome

What are all of the statuses that need to be excluded?

Sam McDonald

Hi Paul they would be:

HCP Added to HCPIS - pending NH approval

EN Sent

Self-Certification sent

EN Completed

Details sent to ETM

Awaiting draft contract - ETM

ETM Contract Received

Paul Newcome

You will need to add the rest of the statuses and update the column names using the below syntax…

=IF(OR([# Column]@row = 0, AND(OR(HAS([Status Column]@row, "ETM Contract Signed"), HAS([Status Column]@row, "Direct Contract Signed")), NOT(HAS([Status Column]@row, "1st Exclude Status")), NOT(HAS([Status Column]@row, "2nd Exclude Status")), NOT(HAS([Status Column]@row, "3rd Exclude Status")), ………………………………., NOT(HAS([Status Column]@row, "Final Exclude Status")))), 1)

Sam McDonald

Amazing, thanks so much Paul!

Just one other thing, I noticed I don't have the right layout for the first part of the formula. Instead of it being # of contracts is 0, how would I get it to check if two columns have the same number? So essentially if the figures match in both columns and the status is either one of the signed options then tick the box.

I've tried this but it ticks the box:

=IF(OR([# ETM & Direct (FR) Contracts]@row = [Count of French Contract Status]@row), AND(OR(HAS([French Contract Status]@row, "ETM Contract Signed"), HAS([French Contract Status]@row, "Direct Contract Signed")), NOT(HAS([French Contract Status]@row, "ETM CONTRACT RECEIVED")), NOT(HAS([French Contract Status]@row, "HCP Added to HCPIS - Pending NH approval")), NOT(HAS([French Contract Status]@row, "EN sent")), NOT(HAS([French Contract Status]@row, "Self-Certification sent")), NOT(HAS([French Contract Status]@row, "EN completed")), NOT(HAS([French Contract Status]@row, "Details sent to ETM")), NOT(HAS([French Contract Status]@row, "Awaiting draft contract - ETM")), NOT(HAS([French Contract Status]@row, "Awaiting DD approval")), NOT(HAS([French Contract Status]@row, "Unsigned contract sent to HCP - for cumul d'activitie application")), NOT(HAS([French Contract Status]@row, "Awaiting more information")), NOT(HAS([French Contract Status]@row, "Authorisation - sent for signature")), NOT(HAS([French Contract Status]@row, "Direct contract sent")), NOT(HAS([French Contract Status]@row, "Contract updated on HCPIS")), NOT(HAS([French Contract Status]@row, "Unsigned contract ssubmitted to CNOM")), NOT(HAS([French Contract Status]@row, "Signed contract submitted to CNOM")), NOT(HAS([French Contract Status]@row, "HCP Added to HCPIS")), NOT(HAS([French Contract Status]@row, "HCP on HCPIS"))), 1)

Paul Newcome

You have a closing parenthesis in the wrong place. You would use the same syntax, but instead fo saying equal to zero, you would say equal to the other cell.

=IF(OR([# Column]@row = 0, AND(OR(HAS([Status Column]@row, "ETM Contract Signed"), HAS([Status Column]@row, "Direct Contract Signed")), NOT(HAS([Status Column]@row, "1st Exclude Status")), NOT(HAS([Status Column]@row, "2nd Exclude Status")), NOT(HAS([Status Column]@row, "3rd Exclude Status")), ………………………………., NOT(HAS([Status Column]@row, "Final Exclude Status")))), 1)

Sam McDonald

Hi Paul, sorry but I still can't seem to get it to work properly. Ideally the box shouldn't be ticked for options 2-4 below as even though the numbers match the status isn't exclusively one of the signed options.

=IF(OR([# ETM & Direct (FR) Contracts]@row = [Count of French Contract Status]@row, AND(OR(HAS([French Contract Status]@row, "ETM Contract Signed"), HAS([French Contract Status]@row, "Direct Contract Signed")), NOT(HAS([French Contract Status]@row, "ETM CONTRACT RECEIVED")), NOT(HAS([French Contract Status]@row, "HCP Added to HCPIS - Pending NH approval")), NOT(HAS([French Contract Status]@row, "EN sent")), NOT(HAS([French Contract Status]@row, "Self-Certification sent")), NOT(HAS([French Contract Status]@row, "EN completed")), NOT(HAS([French Contract Status]@row, "Details sent to ETM")), NOT(HAS([French Contract Status]@row, "Awaiting draft contract - ETM")), NOT(HAS([French Contract Status]@row, "Awaiting DD approval")), NOT(HAS([French Contract Status]@row, "Unsigned contract sent to HCP - for cumul d'activitie application")), NOT(HAS([French Contract Status]@row, "Awaiting more information")), NOT(HAS([French Contract Status]@row, "Authorisation - sent for signature")), NOT(HAS([French Contract Status]@row, "Direct contract sent")), NOT(HAS([French Contract Status]@row, "Contract updated on HCPIS")), NOT(HAS([French Contract Status]@row, "Unsigned contract ssubmitted to CNOM")), NOT(HAS([French Contract Status]@row, "Signed contract submitted to CNOM")), NOT(HAS([French Contract Status]@row, "HCP Added to HCPIS")), NOT(HAS([French Contract Status]@row, "HCP on HCPIS")))), 1)

Sam McDonald

I've used a workaround in adding extra columns to split up the parts of the formula and this now seems to be working as expected. Thanks!

Paul Newcome

Glad you got it sorted. The reason for rows 2 and 4 being checked are because of the very first part of your formula.

=IF(OR([# ETM & Direct (FR) Contracts]@row = [Count of French Contract Status]@row, ………………………..), 1)

Both of those rows meet the first criteria of the OR where the numbers in the two columns equal each other.