Cross sheet formula, check the box in one sheet when ALL boxes are checked in another.
I have a three sheet cascade of check boxes that have cross sheet formulas.
 Mother Plant sheet
 Mother Segment sheet
 Mother Batch sheet
There are multiple Plants per Segment; multiple Segments per Batch.
I "Retire" plants as necessary, sometimes all at once and sometimes at different times. I want the "Retire" check box to automatically check in one sheet when ALL boxes of a certain indexed row are checked in another.
The formula I am using will check the box when ONE of the corresponding boxes is checked 03MOTHER PLANT SHEET =COUNTIFS({mom tag}, [CLTVTD MOTHER TAG]@row, {SEG ADDY}, [Segment Address]@row, {retired}, 1)
MOTHER BATCH SHEET =COUNTIFS({mom tag}, [CLTVTD MOTHER TAG]@row, {RETIRED}, 1)
The (....{RETIRED}, 1) will check the box if any one box matches, but what I am looking for is something that will ONLY check when ALL of the matching boxes have been checked.
See screenshots for example.
 Individual PLANT sheet as I retire plants individually.
You will notice that they all have the same "CLTVTD MOTHER PLANT TAG" Number, But they have different "Segment Addresses".
When all of the plants in a particular "segment address" are checked then I want the corresponding segment address in a different sheet to automatically check the box.
Based on the first picture, the first two boxes in this following picture should be checked, but the following three would not be checked yet because ALL of the boxes for "Segment 6, Leg 11  Baby Jokers" are not checked.
This cascades into the "Batch" level sheet so that once ALL of the boxes from batch number "1A4060200007660000000048" are checked (Which requires all of the boxes in that segment to be checked, which requires all of the mothers in that segment to be checked), then the "Retired" box in the "MOTHER BATCH" sheet will check as well.
I hope all that makes sense. Thanks so much for your help with this.
Answers

Hi @Sarah_lee123 ,
I would suggest adding another column (which can then be hidden) on your second sheet. This would count the Segment Address from the first sheet and be used as the basis for a follow up formula.
The formula would be:
=COUNTIF({03MOTHER PLANT SHEET (Segment Address)},Segment Address@row)
I.e. counting the segment addresses on the first sheet that match on the second. I will refer to this column as "Segment Count" in the formula later on.
You can then use a IF & COUNTIFS formula combo to check if all the lines are retired in a range in your 05MOTHER SEGMENT Retired column:
=IF(COUNTIFS({03MOTHER PLANT SHEET (Segment Address)},[Segment Address]@row,{03MOTHER PLANT SHEET (Retired?)},[Segment Count]@row,1,0)
If all parts of segment are retired in sheet 03, then this should make the box ticked in the 05 sheet.
You can use a similar principle in the 05 to 07, adding a column for segment counts to 07 etc.
Hope this makes sense and helps  if you have any further questions please ask!

Thanks @Nick Korna
I added a "Segment Count" column. But when I rendered the formula, it did not work out. I am thinking there needs to be something more in the formula. If all "2" of the segments are checked then check this box as well.
=IF(COUNTIFS({SEG ADDY}, [Segment Address]@row, {retired}, [Segment Count]@row, 1, 0))

I'm not 100% sure where your references are pointed at, so I'll make an easier to follow example (because deciphering that code of mine a week later is not a joy!).
With 2 sheets:
Leg on this sheet would correspond to Segment Address on your sheets 03 & 05.
The formula on this 2nd sheet for the Retired? column is:
=IF(COUNTIFS({Legs Range 1}, Leg@row, {Legs Range 2}, 1) = [Segment Count]@row, 1, 0)
The cross sheet formula ranges are (the other columns are irrelevant for these):
{Legs Range 1}, which corresponds to {SEG ADDY)} in your formula above:
And for {Legs Range 2}, or {retired} in your formula:
Hoping this makes it a bit clearer?

That worked great! Thank you for that. I have a next level question now pertaining to the same formula we have been working on.
I actually need this formula to search for all checked boxes from two different sheets. Basically, I need to repeat this formula (within the same formula?) and say "If all boxes with given criteria are checked in both Sheet 1 AND Sheet 2, THEN check the box in the corresponding third sheet. All three sheets will match the same set of criteria in order to check the boxes.
Does that make sense?
Thanks so much for your help on this one, it is a pretty big crux I need to clear.

If the extra sheet is similar (segment parts + a check box) then you can alter the above formula slightly to incorporate the extra set of criteria from the extra sheet (referred to as Sheet 2 for 2nd set of criteria):
=IF(AND(COUNTIFS({Legs Range 1}, Leg@row, {Legs Range 2}, 1) = [Segment Count]@row), (IF(COUNTIFS({Sheet 2 Range 1}, Leg@row, {Sheet 2 Range 2}, 1) = [Segment Count]@row, 1, 0)))
The ranges are the same as on the original Legs Range  range 1 on the left (Leg column) and 2 on the right (tick box).
The box for a given segment on your final sheet will then tick only when the boxes on both the other 2 sheets are ticked.
Does this resolve your query?

Hello,
@Nick Korna Thanks so much for sticking with me on this.
It's still not picking up. There is more to the story, but I was hoping to get this sorted out without having to tell a long complicated story, but I believe that you will require more of the story to help solve this problem.
I think it's not working because the segment count is only pulling from one source sheet instead of both. So, in order for the checkbox situation to work, I will need the "segment count" column to count the associated rows from BOTH sheets.
Current formula for Segment Count Column (Mother Segment Sheet) pulling from one (Mother Plant Sheet) of the two (Retired Mother Plant Sheets) sheets =COUNTIF({SEG ADDY}, [Segment Address]@row), but in order to count from both sheets, I wrote =COUNTIFS({SEG ADDY}, [Segment Address]@row, {SEG ADDRESS},[Segment Address]@row) but I am receiving an error message with this version of the formula. Should I be using an AND formula?
________________________________________________
I tried to use the formula you provided above. =IF(AND(COUNTIFS({SEG ADDY}, [Segment Address]@row, {retired}, 1) = [Segment Count]@row), (IF(COUNTIFS({SEG ADDRESS}, [Segment Address]@row, {RET}, 1) = [Segment Count]@row, 1, 0)))
(Not "LEG", but "Segment Address")
I checked ALL boxes for the corresponding Segment Address in both Mother plant sheet and Retired Mother Plant Sheet". I pulled it into the Mother Segment Sheet from other two sheets, but I receive a blank cell where there should be a checkbox. I think it's because it is counting the segments from one, not both, sheets.
See screenshots for Segment Address "Segment 4, Leg 8  Purple Milk"
1. Mother plant sheet  this is where the Segment Count is pulling from. (Note that there are 6 plants in this sheet, and these 6 plants show up in the segment count in the Mother Segment Sheet  photo #3)
2. Retired Mother Plant Sheet  this is the second sheet where I need the segment count to ALSO count from. (Note that there are 3 plants in this sheet, and these 3 plants do NOT show up in the segment count in the Mother Segment Sheet  photo #3)
3. Mother Segment Sheet  This is the sheet that I am trying to get the retired box to check once ALL boxes are check in both of the above sheets. (Note that there should be 9 plants in total. Once the plants move from the Mother plant sheet to the Retired Mother Plant sheet (Row is moved to "Retired Mother Sheet" when mother gets retired). The Segment count is no longer correct as it reduced the number from 9 to 6 when the 3 plants got retired.
I hope this help clarify my issue. I know it's a lot of information to process and I really appreciate your help on this!
Thanks a ton,
Sarah
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.6K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!