Adapting Conditional Formatting Formula for Cross Sheet Reference

Question

I currently have a sheet setup for work that compares 3 columns of data with conditional formatting. The goal is to identify whether the same string of characters can be found 1 2 or 3 times across all 3 columns. I have been using the following conditional formatting formulas =countif(A:C,A1)=1 and I have 2 more formulas with =2 and =3 at the end. It works perfectly for my needs except I would like to be able to sort/filter each column separately which Google Sheets doesn't seem capable of doing. So my thought was to separate each column into a separate sheet. The only issue is I can't figure out the proper way to make the conditional format work cross-sheet and still give me the needed results of identifying the same information 1-2-3 times spread out over 3 sheets. I tried something like =countif(Sheet1!:A,A1)=1 but it doesn't seem to be a valid formula and I'd need to check all 3 sheets, not just one. What I essentially want to do is replace the A:C part of the original formula with Sheet1:Sheet3 but I'm not sure how or if that's possible. It may not even be a possibility but figured this was the place to ask.

Answer 1

• conditional formatting can't intake a cross-sheet reference without INDIRECT therefore try:

  =COUNTIF(INDIRECT("Sheet1!A1:A"), A1)=1

• and from there you can build an array:

  =COUNTIF({INDIRECT("Sheet1!A1:A");
            INDIRECT("Sheet2!A1:A");
            INDIRECT("Sheet3!A1:A")}, A1)=1