checkbox from reference sheet, multiple columns

Julien Lopez

Hi community

i'm looking for a formula to automatically check boxes associate to dates from a reference sheet with multiple column of check boxs and dates.

here are my tables:

table 1

table 2 (if the box is checked, a date appears in the column at the right.)

i want that the table 1 automatically check the boxes related to the appropriate dates when boxes are checked in the table 2.

i did a first formula with the columns about saturday:

=IF(COUNTIFS({Master rotation weekend name}, [Main Tech]@row, {Master rotation weekend Sat 1 check}, =1, {Master rotation weekend Sat 1}, Start@row) > 0, 1)

but with two or more columns in the table 2 to integrate in the formula… i'm lost

thank you

kowal

hi @Julien Lopez,

I am bit confused why do you use COUNTIFS inside IF formula?

As COUNTIFS gives you only O or 1 would not it be easier to use only IF formula but with range outside of the main sheet? and if there are few dates you need to check you can make NESTED IF formula. I think it woulbe bit easier to cope with.

Let me know what you think.

Julien Lopez

thanks @kowal

ok, IF should be easier but i'm still a bit lost because a need that dates match between the both tables to check the right box in table 1.

could you give me an exemple of the good formula? not sure if a need a AND or a OR in it.

and, i had to change the columns type of the table 2 so now there is a "X" instead of a check box.

look like that:

so, in table 1, 1-jan-2025 and 4-jan-2025 check box should be checked:

thank you so much

kowal

@Paul Newcome you always destroy such issues in a second.

Paul Newcome

I would just use the same logic as in the original formula with the IF/COUNTIFS, but I would use an OR or add them together for each.

=IF(COUNTIFS(first check / date) + COUNTIFS(second check / date) + COUNTIFS(third check / date) > 0, 1)

or

IF(OR(COUNTIFS(first check / date) > 0, COUNTIFS(second check / date) > 0, COUNTIFS(third check / date) > 0), 1)