Formula Help - Conflict Check Across Multiple Sheets

Sam Nies

Greetings!

I have been trying to figure out a formula that will solve a problem. I have a column in schedule that I would like to show "Conflict" if the contact@row is scheduled on the current sheet and on a different sheet within the same date range.

I have an easy conflict check for whether someone is scheduled on the same sheet within the same sheet =IF(AND(ISBLANK([Guide 1]@row) = false, COUNTIFS([Guide 1]:[Guide 1], [Guide 1]@row, [Start Date]:[Start Date], [Start Date]@row) > 1), "Conflict", "")

I just also need to know if the employee is scheduled on this sheet and another. Is my formula adaptable?

Thanks all!

Kelly Moore

Hello @Sam Nies

Yes, your formula is adaptable! You will need to add additional COUNTIFS for each sheet and add all the COUNTIFS together. If the Sum of all of them is greater than 1, you will know your contact name exists on one or multiple sheets. If you need to know which other sheet - well then we have to begin rearranging your formula.

=IF(AND([Guide 1]@row<>"", COUNTIFS([Guide 1]:[Guide 1], [Guide 1]@row, [Start Date]:[Start Date], [Start Date]@row)+COUNTIFS({sheet 2 Guide column},[Guide 1]@row,{sheet 2 Start Date column},[Start Date]@row) > 1), "Conflict", "")

You can continue adding COUNTIFS as needed. Remember since this uses cross sheet references, you will need to manually create references, you cannot simply copy paste the formula.

Will this work for you?
Kelly

Sam Nies

@Kelly Moore Thank you!

Your formula worked to show if they contact was found on my other sheet but not just on the start date for some reason I haven't seen yet. I think I'm on the right track now though. I need to include a date range in this new formula as well. My first formula is just for one day "Start Date" column. I need to search for a conflict for the contact on dates that are "End Date > Start Date" on both sheets.

Kelly Moore

hey @Sam Nies

I wondered about an End Date. Add the {End Date} range also to your COUNTIFS with a criteria @cell < = [End Date]@row and add it again with {End Date} range, @cell>[Start Date]@row

Will that work?

Kelly

Sam Nies

@Kelly Moore

Here's what I tried

=IF(AND([Guide 1]@row <> "" COUNTIFS([Guide 1]:[Guide 1], [Guide 1]@row, [Start Date]:[Start Date], [Start Date]@row >= [End Date]:[End Date], [End Date]@row) + COUNTIFS([Guide 1]:[Guide 1],{Guide 1}, [Start Date]:[Start Date], {MDT Start Date} < = [End Date]:[End Date], {MDT End Date} > 1, "Conflict", "")

It's giving me #UNPARSEABLE error

Any suggestions?

Kelly Moore

Hey @Sam Nies

When formulas have the correct number of parentheses, the final parenthesis will be blue. If you look at the formula and find the Leading Green Parenthesis, you see it is for the 2nd COUNTIFS function. So you need to close that COUNTIFS function, as well as the AND function.

Try this:

=IF(AND([Guide 1]@row <> "" COUNTIFS([Guide 1]:[Guide 1], [Guide 1]@row, [Start Date]:[Start Date], [Start Date]@row >= [End Date]:[End Date], [End Date]@row) + COUNTIFS([Guide 1]:[Guide 1],{Guide 1}, [Start Date]:[Start Date], {MDT Start Date} < = [End Date]:[End Date], {MDT End Date}) > 1), "Conflict", "")

Does that work for you?
Kelly

jmyzk_cloudsmart_jp

Hi @Sam Nies

Here is a demo solution.

In the image below, a report within a sheet, a schedule conflict, has a dark red bar, and a conflict with another sheet, sample_schedule_2, has a light red bar.

https://app.smartsheet.com/b/publish?EQBCT=b8c4123d7ff24775964e767b57ffc836

Below is the sheet, sample_schedule_1, checking the conflict within and with another sheet.

https://app.smartsheet.com/b/publish?EQBCT=26794a522af645d9844c6d3d34f75551 (A filter is applied in the published demo sheet in the image below to ease the check.)

Formulas

[Conflict] =IF([Count Total]@row <> [Count Left]@row + [Count Right]@row, 1)
[Count Total] =COUNTIF(COLLECT(Start:Start, Row:Row, <Row@row, [Guide 1]:[Guide 1], [Guide 1]@row), ISDATE(@cell))
[Count Left] =COUNTIF(COLLECT(Start:Start, Row:Row, <Row@row, [Guide 1]:[Guide 1], [Guide 1]@row), >End@row)
[Count Right] =COUNTIF(COLLECT(End:End, Row:Row, <Row@row, [Guide 1]:[Guide 1], [Guide 1]@row), <Start@row)[

The formula logic checks for scheduling conflicts by analyzing rows with the same [Guide 1] value. It compares the total count of rows ([Count Total]) to the sum of rows that start after the current row's end ([Count Left]) and rows that end before the current row's start ([Count Right]). If these counts do not match, it indicates an overlap or conflict.

1. [Count Total] counts rows with the same [Guide 1] value where the row number is less than the current row and the [Start] column has valid dates.
2. [Count Left] counts rows where the [Start] date is later than the [End] date of the current row.
3. [Count Right] counts rows where the [End] date is earlier than the [Start] date of the current row.
4. [Conflict] evaluates if [Count Total] is not equal to the sum of [Count Left] and [Count Right]. If they don’t match, it sets the value to 1, indicating a conflict.

I used the helper columns [Count Total], [Count Left], and [Count Right] to make the formula's logic easy to understand, but you can combine them into a formula that does not use helper columns.

[Conflict w/o Helper] =IF(COUNTIF(COLLECT(Start:Start, Row:Row, <Row@row, [Guide 1]:[Guide 1], [Guide 1]@row), ISDATE(@cell)) <> COUNTIF(COLLECT(Start:Start, Row:Row, <Row@row, [Guide 1]:[Guide 1], [Guide 1]@row), >End@row) + COUNTIF(COLLECT(End:End, Row:Row, <Row@row, [Guide 1]:[Guide 1], [Guide 1]@row), <Start@row), 1)

This logic identifies whether any prior row overlaps with the current row’s [Start] and [End] dates, signaling a scheduling conflict.

The same principle applies to checking conflicts across multiple sheets using additional range references.

[Sample 2 Conflict] =IF(COUNTIF(COLLECT({sample_schedule_2 : Start}, {sample_schedule_2 : Guide 1}, [Guide 1]@row), ISDATE(@cell)) <> COUNTIF(COLLECT({sample_schedule_2 : Start}, {sample_schedule_2 : Guide 1}, [Guide 1]@row), >End@row) + COUNTIF(COLLECT({sample_schedule_2 : End}, {sample_schedule_2 : Guide 1}, [Guide 1]@row), <Start@row), 1)