Checkbox formula matching data from another sheet
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!!
Best Answer
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@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?
-
@Andrée Starå do you have any thoughts on my last request/idea?
Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!