Checkbox formula matching data from another sheet

Andrea Westrich

I would like to create a formula to verify data from another sheet and check a box if true.

Here are the details:

This is sheet 1 where Contract Sent and Contract Received are checkboxes.

Here is sheet 2 where I have Envelope Status and the matching column of Contract Contact Name.

Is it possible to write a formula in the Contract Sent checkbox cell that says, If Contract Contact name @row, matches Contract Contact Name of Sheet 2 and Envelope Status is "Sent", check the box?

If so, what is the format of that formula? I know how to select the columns of the other sheet, but what is the format, order and functions to get that done?

Thank you so much!!

Andrée Starå

Hi @Andrea Westrich

I hope you're well and safe!

Try something like this.

=IF(INDEX({Envelope Status}, MATCH([Contract Contact Name]@row, 
{Contract Contact Name}, 0)) = "Sent", 1)

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @Andrea Westrich

I hope you're well and safe!

Try something like this.

=IF(INDEX({Envelope Status}, MATCH([Contract Contact Name]@row, 
{Contract Contact Name}, 0)) = "Sent", 1)

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrea Westrich

@Andrée Starå, This worked perfectly!! Thank you so much!!

I'd like to add a tweak to it. I have two columns, Contact Sent and Contract Received for which the criteria in quotes are different. So when I used the fantastic formula you gave me it works perfectly for each perspective column.

The process is, when a contract is sent, the Envelope Status says "Sent" and the Contract Sent column checkbox with your formula is checked. Then, when the Envelope status changed to "Completed" the next column, Contract Received, with your formula checks.

All works well, except, once the checkbox in Contract Sent is checked, I'd like it to stay that way, regardless of what the Envelope status changes to after that. Is that possible?

Andrea Westrich

@Andrée Starå do you have any thoughts on my last request/idea?

Thank you!