CHECK BOX IF TWO CELLS MET THE CRITERIA
Hello,
as you can see, I have a form and if a employee selected a certain date and certain time, what is the formula to tick or check the box on the circled part of the picture.
Please share with me the formula.
Thank you
Answers
-
Hey @eventsagp
The way you have your sheet laid out, you will need an individual formula for every checkbox column you have. Smartsheet doesn't have a way to associate column names with information contained within the sheet. In other words, your column title says Sunday but that doesn't mean anything to smartsheet,
Is this sheet only for say, a one week event, or will the columns continue forever? Also, do you need to know the time, or is the checkbox just an indication that Sunday, for instance, is selected?
If you're looking for a count, a report will give you the data easily, with no formulas.
Please let me know the answer to if the weeks are limited, particularly to only 1 week, or go on forever, and if time is actually important.
Kelly
-
I made it quite simple, like I have two sheets Sheet 1 are information from the form, and sheet 2 is the schedule status.
Sheet 1 below
Sheet 2
Then how can I link and automate it to formula if both criteria are met.
-
Hey @eventsagp
Different functions are used when using cross sheet references and IF statements.
Is your Sheet2 set up in a Parent Child hierarchy? It's difficult to tell from the Sheet2 screenshot if the yellow row is a Parent row. If yes to the hierarchy structure, please add one Date helper column. This would allow your formula to be dynamic so that you wouldn't have to update it daily with a date.
If yes to the hierarchy, please add this formula to the Date Helper column that you just added to Sheet2
=IF(COUNT(CHILDREN())>0, Time@row, PARENT())
Below is the schedule formula you want
=IF(COUNT(CHILDREN())=0,IF(COUNTIFS({Sheet 1 Cabin 1 - schedule slot},[Date Helper]@row,{Sheet 1 C-1 Timing},Time@row)>0,"Booked", "Available"))
If no to the hierarchy, the schedule formula is below and will need to be updated with the date.
IF(COUNTIFS({Sheet 1 Cabin 1 - schedule slot}, DATE(2023,12,31), {Sheet 1 C-1 Timing},Time@row)>0,"Booked", "Available")
Does this work for you?
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!