When a box is checked in source sheet, check box for all records in 2nd sheet where a match is found
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
Answers
-
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
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
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.
-
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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!