Check Boxes and Cross-Sheet References

Matt Desjardin

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!

Jeff Reisman

@Matt Desjardin

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)

Jeff Reisman

@Matt Desjardin

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)

Matt Desjardin

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

Jeff Reisman

https://community.smartsheet.com/discussion/comment/360143#Comment_360143

Excellent, glad it worked!