CHECK BOX IF TWO CELLS MET THE CRITERIA

eventsagp

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

Kelly Moore

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

eventsagp

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.

Kelly Moore

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