1

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.

migrated from superuser.com Feb 27 at 17:23

This question came from our site for computer enthusiasts and power users.

0
  • 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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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