Identify Duplicate Values On Select Dropdown Selections

Options

I want to identify any new rows that are trying to use the same filename as an existing row. We do have reuses, so I do not want those flagged. I was able to set up a checkbox helper column for Duplicates that identifies any filename that is repeated in that column.

=IF(COUNTIF([Filename]:[Filename], [Filename]21) > 1, 1, 0) 

If a duplicate filename is used, it flags that row in the Duplicate column.

I then set up a conditional formatting to change the cell color to red for any Filename that has the Duplicate column flagged.

However, I want to refine this based on a Classification column. We reuse files under the same filename, and I do not want those flagged.

I updated the conditional formatting to exclude anything with Reuse in the classification column, but that leaves the original row flagged.

Essentially, I want to flag any duplicate Filename, but only if they meet one of two conditions in a different Classification column: New or No Research.

Any suggestions as to how to get the rest of the way solving this?

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!