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
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 443 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
