When a box is checked in source sheet, check box for all records in 2nd sheet where a match is found

Sblackbu

Hi Community,

Can someone provide a recommendation as to how I can revise the below formula. I'm trying to perform a match using a two match criteria across two sheets. define a condition that when a box is checked in the source sheet and a lookup is performed in sheet #2 he Match function to match on two criteria's between two sheets

Essentially when the check box "Partner Signature Confirmed" in source sheet "Partner Supervisor Sing-off.." is checked, a lookup is performed in sheet #2 ("4.Table Monthly Inv...". for the associated "Contract ID" to find a match of all records where the "Contract ID", "Payroll Invoicing Date" in the source sheet, matches the "Contract ID" and "Date Partner Supervisor Signature Requested" date in sheet #2, "4.Tabley Monthly Inv...". When a match is found, the "Partner Signature Confirmed" box in sheet #2 should be auto checked for all records that meet the criteria . 

=IFERROR(INDEX({Partner Sig Confirmed_Metric Sheet}, MATCH([Contract ID]@row, {Contract ID_Metric Sheet}, 0), MATCH([Date Partner Supervisor Signature Requested]@row, {Payroll Invoicing Date_PartnerSupervisor Signoff}, 0)), "")

**please note that the column "Contract ID" is a unique value that has a one to many relationship with

Screenshot 2024-02-15 at 4.52.49 PM.png

Screenshot 2024-02-15 at 4.54.54 PM.png

Bassam Khalil

Hi @Sblackbu,

I've created two sheets to test the formula, and I hope the following details will assist you in resolving your issue:

Sheet T2 is designated for the "Partner Signature Confirmed" checkbox. Here is a screenshot of it:

Sheet T1 lists all contract IDs. The "Partner Signature Confirmed" box in Sheet T2 should be automatically checked for all records that meet the specified criteria. Here is a screenshot of this sheet:

To match criteria across the two sheets, I utilized the following formula in sheet T1 in column [ Partner Signature Confirmed ] and i convert it to column formula:

=IFERROR(IF(INDEX(COLLECT({Partner Signature Confirmed}, {Payroll Invoicing Date}, [Date Partner Supervisor Signature Requested]@row, {Contract ID}, [Contract ID]@row), 1) = 1, 1, 0), "")

please try it and change the reference in the formula to match your sheet setup

Sblackbu

Thanks Bassam!!! I really appreciate that you took the time to look into this. My apologies, I did not realize you'd responded. Unfortunately, the formula is still not working as expected. So your example provided is very similar to my scenario. The problems occurs when there are two Id's that are named the same but the requested date is different. The logic only seems to recognize the first set of records reflecting the Contract ID. So when the checkbox in T2, Partner Signature Confirmed is checked for Contract ID "1", Date Partner Supervisor Signature Requested "12/02/2024" and the checkbox in T2, Partner Signature Confirmed is also checked for Contract ID "1", Date Partner Supervisor Signature Requested "24/02/2024", only the first set of records in the destination worksheet, T1 is checked.

Paul Newcome

Try a COUNTIFS with an IF instead. IF the COUNTIFS (range/criteria sets as needed plus an extra set for the box being checked) is greater than zero, check the box on this sheet.

=IF(COUNTIFS({Checkbox}, @cell = 1, {Contract ID}, @cell = [Contract ID]@row, {Date}, @cell = Date@row)> 0, 1)