How to color-band by duplicate entry

sdugas

Hello! I've looked in a few different spots for this answer, but haven't seen anything that works for my setup.

I run webinars that have the same topic hosted on two different dates. I need to keep the dates in separate rows in Grid View so they show properly in Calendar View. I'd like to color-band the rows by topic instead of every other.

I've tried following this:

1. Select the range of cells you want to apply the color banding to.
2. Click on the "Format" dropdown menu in the top toolbar, and select "Conditional formatting."
3. In the "Conditional formatting" panel that appears on the right, select "Add rule."
4. In the "Add formatting rule" panel, choose "Custom formula is" from the first dropdown.
5. In the text field next to the dropdown, enter the following formula:
6. =countif([Topic Name]:[Topic Name], [Topic Name]@row) > 1
7. This formula counts the number of times the topic name in the current row appears in the entire "Topic Name" column. If the count is greater than 1, it means that the current row has a duplicate topic name.
8. Choose the color you want to use for the formatting in the "Color" dropdown.
9. Click "Save" to apply the formatting.

However, I don't have these options. When I click the "Conditional Formatting" button in the top toolbar, I can only add a new rule based on column and criteria. I can't add a custom formula as steps 4 and 5 mention. Is there a workaround for this? Do I have incorrect settings activated?

Thank you!

Ryan Sides

Where did you see those instructions? It that Chat GPT? :) That's interesting.

The way I do this is add a Column (type checkbox) to your sheet. Call it Dup. Add this formula. Make it a column formula.

=IF(COUNTIF([Topic Name]:[Topic Name], [Topic Name]@row) > 1, 1, 0)

Then in Conditional Format, set it so that when the Dup column is checked, it highlights the row.

sdugas

@Ryan Sides Yep, you got me! It's been awesome for smaller questions on building in SmartSheets.

Your formula worked! And I realize now that I'm asking SmartSheet to do the wrong thing. It's checking all instances of duplicates, where I'm looking for color band by topic. Not necessarily all duplicates. I may need to think more about how to pull this off. Thanks for your help!

Ryan Sides

You bet! Happy to help.