Cross sheet formula, check the box in one sheet when ALL boxes are checked in another.

Sarah_lee123

I have a three sheet cascade of check boxes that have cross sheet formulas.

1. Mother Plant sheet
2. Mother Segment sheet
3. 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 03-MOTHER 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.

1. 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.

Nick Korna

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({03-MOTHER 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 05-MOTHER SEGMENT Retired column:

=IF(COUNTIFS({03-MOTHER PLANT SHEET (Segment Address)},[Segment Address]@row,{03-MOTHER 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!

Sarah_lee123

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))

Nick Korna

@Sarah_lee123,

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?

Sarah_lee123

@Nick Korna

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.

Nick Korna

@Sarah_lee123,

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?

Sarah_lee123

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