Helper checkbox not working on reference sheet

Joseph Aloysias

I have multiple line items for a order in one sheet(Request Sheet) and in another sheet (Order sheet) where I have clubbed all the items in single row, based on the order ID from request sheet to order sheet I want to check the Quantity checkbox and update in the Order sheet, I tried index match formula but its not working

Request Sheet:

Order Sheet:

Hollie Green

The issue is it doesn't know whether to check the box or not check the box because there are multiple ORD0241 on the Request sheet and one is checked and one is not checked. If I am understanding correctly you want the box checked if any of the ORD0241 are checked on the request sheet. If that is correct you would need to use a countifs formula with an If formula

=IF(COUNTIFS({Order ID reference from request sheet},[Order ID]@row,{Quantity Helper reference from request sheet},1)>=1,1,0)

Hollie Green

The issue is it doesn't know whether to check the box or not check the box because there are multiple ORD0241 on the Request sheet and one is checked and one is not checked. If I am understanding correctly you want the box checked if any of the ORD0241 are checked on the request sheet. If that is correct you would need to use a countifs formula with an If formula

=IF(COUNTIFS({Order ID reference from request sheet},[Order ID]@row,{Quantity Helper reference from request sheet},1)>=1,1,0)

Joseph Aloysias

This is working great, thanks.

Can you please explain the formula?

Hollie Green

The countifs formula works by having multiple criteria to count.

=Countifs(range 1, criteria 1, range 2, criteria 2, etc.....) the range and criteria I used in your formula made the formula only count the rows where both the Order number matched and the check box was checked on the same row.

I nested the countifs inside of an If statement as the first part of the logical expression. then told it only if the the countif statement was greater than or equal to 1 should it input the true value of 1.

=If(Logical expression, value if logical espression is true, value if logical expression is false)

=If(the countif formula>=1,1,0)

Hopefully this makes sense nested formulas can be complex to explain.