checkbox from reference sheet, multiple columns
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
Answers
-
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.
The Real Smartsheet Enthusiast
Is there anything else we can help you with? - book your time!
MASA Consult - Your Aligned Smartsheet Partner
Find us on LinkedIn!
-
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
-
@Paul Newcome you always destroy such issues in a second.
The Real Smartsheet Enthusiast
Is there anything else we can help you with? - book your time!
MASA Consult - Your Aligned Smartsheet Partner
Find us on LinkedIn!
-
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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!