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.


1 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:

              INDIRECT("Sheet3!A1:A")}, A1)=1

  • This worked perfectly. Thank you so much! Never would've figured that out lol
    – Joel
    Feb 27, 2019 at 19:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.