Duplicate values on different tabs

[kristell]

Happy New Year everyone!

I am trying to do a two-way duplicate conditional formatting rule. This is for configuration on a case management system where we use searches to create dashboards for users

I have a tab ALLSEARCHES where all 'searches' are listed - the conditional formatting checks the tab SystemDashboards (where all the different dashboards are listed with the applicable search used in that dashboard) to see if the 'search' appears there and this works fine.
=COUNTIF(SystemDashboards!$K$1:$K$1341,$C2)>0
(see first example on screenshot attached where this works)

I also want it to go the other way - to show that the search used in a dashboard is on the ALLSEARCHES tab - to double-check if any searches get updated/renamed etc (I download this list and paste it in to this tab weekly)

The value on SystemDashboards may appear more than once -i.e. it could be used multiple times.

When I added the conditional formatting to the SystemDashboards tab -
=COUNTIF(ALLSEARCHES!$C$1:$C$2553,$K2)>0

It didn't always pick up the duplicate value
(see second example on attachment where the value is only highlighted on the ALLSEARCHES tab and not on the SystemDashboards tab)

Sometimes it only highlighted some of the values on the SystemDashboards tab
(see third example on attachment)

I have reformatted text - copying in to Notepad and back again to 'cleanse' and remove any spaces but it doesn't seem to help.

Attached is a screenshot where I have attempted to show some examples of what is happening.

Any suggestions welcomed!!

Many thanks

 

[coulombevin]

Hi Kristell,
If I understand correctly, you want to highlight text in column K of the "SystemDashboards" sheet if it is found in column C of the "ALLSEARCHES" sheet, and vice versa.

If that's the case, here’s what you need to do:

1. Select the entire column where you want to apply the new conditional formatting rule.
2. Create a new rule using Conditional Formatting > New Rule > Use formula and enter the following formula:

- For the "ALLSEARCHES" tab:

=COUNTIF(SystemDashboards!$K:$K,$C1)>0

- For the "SystemDashboards" tab:

=COUNTIF(ALLSEARCHES!$C:$C,$K1)>0

The issue you encountered is likely due to using a fixed range (e.g., $K$1:$K$1341) and the wrong criteria (e.g., $C2 instead of $C1).

Hope this helps!

Vincent

 

[kristell]

Thank you - I will give this a try!

 

[kristell]

Perfect - thank you so much - appreciate your support more than you will every know.