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.
-
Related :Highlight Rows with Unique Values As Compared to Rows with Same ID# on a Different Sheet – Rubén♦ Feb 27 '19 at 17:33
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 '19 at 19:23