Check Boxes and Cross-Sheet References
Hello Smartsheets Community!
I have possibly a complicated question. I have two sheets, one is essentially a "job log" while the other is an "order sheet" with items for several different jobs. Is it possible to have one box on the job log be checked once all of the items for that job on the order sheet have been checked? As a way to replicate everything has been sourced, so the job can be checked as complete?
I can index/match as such, but it will check off on the job log complete box even if just one item is checked off. But it would need to only check off when all items associated with that job name are checked from the order sheet.
I was using: =(INDEX({Order Confirmed}, MATCH(Job@row, {Jobs}, 0))) in the check box column of the job log currently.
Hopefully that makes sense, I appreciate any input as always! This one has me stumped!
Best Answer
-
The way I would do this would be to use a COUNTIFS formula to count the number of items there are for a given Job, then count the number of rows for that Job where the Order Confirmed box is checked. If they're equal, check the box; if not, don't!
=IF(COUNTIFS({Jobs}, Job@row) = COUNTIFS({Jobs}, Job@row, {Order Confirmed}, 1), 1, 0)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
The way I would do this would be to use a COUNTIFS formula to count the number of items there are for a given Job, then count the number of rows for that Job where the Order Confirmed box is checked. If they're equal, check the box; if not, don't!
=IF(COUNTIFS({Jobs}, Job@row) = COUNTIFS({Jobs}, Job@row, {Order Confirmed}, 1), 1, 0)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hello @Jeff Reisman
That works perfectly! Thanks a ton! I wasn't quite sure how that would need to be written/which formula would make it happen. Appreciate your time looking into this.
Best,
Matt
-
Excellent, glad it worked!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 459 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 137 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
