Overlapping Flag Checkbox

aeparkerson

Hello,

I have created an internal communications form for our executive team to view the messages being pushed to our employees. We have the capability to send messages in a variety of ways, and I want to flag any message that is overlapping in delivery method and date range. So if the delivery method is the same and the start and end date of one particular message overlaps the start and end date of another message, I would like the overlap flag to auto-flag.

If this possible? Below is a screen shot of the grid view.

Genevieve P.

Hi @aeparkerson

If you're looking for an exact match in the Audience column, you could use something like this:

=IF(COUNTIFS(Audience:Audience, Audience@row, [Start Date]:[Start Date], <=[End Date]@row, [End Date]:[End Date], >=[Start Date]@row) > 1, 1, 0)

However, I see that's a multi-select column.

Will there be instances where multiple values are selected? If the values aren't exactly the same, this formula won't recognize them as matching rows:

You could add in a HAS Function that looks to see if the current row's cell has values that are contained in another row:

=IF(COUNTIFS(Audience:Audience, HAS(@cell, Audience@row), [Start Date]:[Start Date], <=[End Date]@row, [End Date]:[End Date], >=[Start Date]@row) > 1, 1, 0)

However this will only flag the one row where all Audience selections are matched somewhere else, and not the row where there are additional criteria.

Let me know if this makes sense and will work for you!

Cheers,

Genevieve

aeparkerson

Hi Genevieve,

So the column (Delivery Method) is a drop down column, but not multi-select. I would like a formula to auto-flag if the Delivery Method "Harri clock-in messages" overlaps with the start/end date of another "Harri clock-in messages."

I am using the following formula, but am getting the #unparseable error.

=IF(COUNTIFS([Delivery Method]:[Delivery Method], @cell = “Harri clock-in messages”, [Start]:[Start], @cell <= [End]@row, [End]:[End], @cell >= [Start]@row) > 1, 0)

Attached is an example of what I would like the formula to do exactly. Line 1 & 3 overlap, and the flag appears.

Genevieve P.

Hi @aeparkerson

Ah my apologies! I was looking at the wrong column. That's great news and will make this much simpler. For your formula, I notice that the quotes are angled instead of straight up and down... “ versus "

Smartsheet needs quotes to be " straight. Try re-typing the quotes directly into Smartsheet to see if that resolves the error! You also don't need square brackets around single word column names:

=IF(COUNTIFS([Delivery Method]:[Delivery Method], @cell = "Harri clock-in messages", Start:Start, @cell <= End@row, End:End, @cell >= Start@row) > 1, 0)

Cheers,

Genevieve