Checking a box based on data from a cell in a different sheet
Hello,
I have two sheets. On one I have a column noting the status of a contract, in drop-down style. I would like for when the status is complete on this sheet, for it to check a box on another sheet. Is this possible?
Best Answer
-
Following the appropriate steps to create cross sheet references, you should be able to make it work with a formula such as...
=IF(INDEX({Master Sheet Mkt. Status Column}, MATCH([Vendor ID]@row, {Master Sheet Vendor ID Column}, 0)) = "Complete", 1)
The above is assuming that the Vendor ID is unique to that row. If it is not and additional data needs to be accounted for to ensure that only the data from the single specified row is pulled, you would replace the INDEX/MATCH with a JOIN/COLLECT.
Answers
-
Hi Tanya,
Yes, it’s possible with a cross-sheet formula.
Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
I hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
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.
-
Andree,
In the Mkt Status column on this one, when the drop down "Complete" is selected.....
I would like it to check the box on this one in the Signed for 2020 Column
Thanks for your help!
-
make the Signed for 2020 a function using a cross-sheet reference to the Mkt Status field
-
Yes this is what I am trying to do, however I'm not sure how to write it as it also needs to find the correct vendor by vendor # or name. I know VLOOKUP and IF, but not together, or guessing there is an easier way?
-
Did you get it working?
Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
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.
-
Hi Andree,
The files are too big to delete sensitive info and share, will the screen shots above work?
-
Andree,
I figured this out and shared copies of both to you. When the Mkt. Status on the rebate tracker is marked complete, I'd like it to check the box on the Marketing Suppliers sheet.
-
Following the appropriate steps to create cross sheet references, you should be able to make it work with a formula such as...
=IF(INDEX({Master Sheet Mkt. Status Column}, MATCH([Vendor ID]@row, {Master Sheet Vendor ID Column}, 0)) = "Complete", 1)
The above is assuming that the Vendor ID is unique to that row. If it is not and additional data needs to be accounted for to ensure that only the data from the single specified row is pulled, you would replace the INDEX/MATCH with a JOIN/COLLECT.
-
That's what I needed Paul, thank you! Again.......
-
Happy to help! 👍️
-
Glad you got it working and thanks for sharing!
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.
-
Hi @Paul Newcome & @Andrée Starå ,
I have a similar question to Tanya's.
One Sheet1, I have multiple tasks with a checkbox associated with each task. When all tasks are complete (and all checkbox's are checked), I want this to be reflected in Sheet2. Sheet2 has a Status column, and I want the status to be changed to Complete once all checkbox's are checked. The Status column is a dropdown with other values (Not Started, In Progress, Complete)
Thank you for any help/advice!
-
@HannahLoc You are going to want something along these lines...
=IF(COUNTIFS({Source Sheet Checkbox Column}, @cell = 1) = 0, "Not Started", IF(COUNTIFS({Source Sheet Checkbox Column}, @cell <> 1) = 0, "Complete", "In Progress"))
-
@Paul Newcome that worked! thank you so so much. never would've gotten there in the direction I was heading lol.
-
No worries and happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!