# Adapting Conditional Formatting Formula for Cross Sheet Reference

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

• 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