Excel Conditional Format across sheets

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hey there, I have been racking my limited knowledge of excel trying to work this out but cant get it right! I have searched and searched but just cant solve it!

I have a list of phone numbers on sheet1 and another list of phone numbers in sheet 2. What I want is to make the excel sheet highlight any duplicates across the various sheets. (I am expecting through time I will have more sheets with numbers on).

I have done the formula =COUNTIF($A$1:$A$100,A1)>1 and that works for highlighting the duplicates on that sheet. But it wont do cross sheets. I have tried this as well: =OR(COUNTIF(sheet1,A1)=1,COUNTIF(sheet2,A1)=1) but that didnt seem to work.

Any advice, in a broken down idiots guide, would be greatly appreciated!

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Conditional format does not work across sheet.
If all you care is that find out if a number appears more than once across sheets then you can use a match

on every sheet you can have a formula like this


=SUM(IF(ISERROR(MATCH(A2,Sheet1!A:A,0)),0,1), IF(ISERROR(MATCH(A2,Sheet2!A:A,0)),0,1))

If basically sums the number of occurrence of the number in cell A2 in the sheet where you have this formula, across sheet1, and sheet2
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
By the way. earlier I had a comma at the of formula. That was wrong. The correct text should be
=IF(ISERROR(MATCH(A2,Sheet1!A:A,0)),0,1)

Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://wikisend.com/ ,https://accounts.google.com/ServiceLogin?passive=1209600&continue=https://docs.google.com/&followup=https://docs.google.com/&emr=1 http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
Ok, think I have made a decent enough mock up of my workbook! (For info I am at home on a Excel 2007 program but work (where I aim to use the formula) is 2003. I have saved the file as 2003 though.

What I want:
1) When I get a new phone number I put it at the foot of the list on SHEET1. I want this to highlight if it is already in the list on SHEET1. This I can do fine using conditional formatting no issues there.
2) Then as I put new data into Sheet2 onwards I want excel to highlight all the phone numbers in the TO and FROM columns if they are already on SHEET1.

Basically SHEET1 is a list of company phones we issue or employees have. They give us the number I document it on sheet1. Then I put their monthly usage onto sheet2 for employee 1, sheet3 for employee 2 and so on. What I want to see is in the call data (sheets2 onwards) is highlights of calls from the caller to another employee as they dont need to pay for those calls.

Hopefully makes sense!

The file is here:
[URL=http://wikisend.com/download/590956/Book2.xls]Book2.xls[/URL]
http://wikisend.com/download/590956/Book2.xls
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Ok Here is the issue. If you want to highlight a cell in Sheet2 based on fact if that number is on sheet1. As I said, that is not directly possible. You have to have a formula based columns on your sheet2. That column will do a check in the number that you are trying to check is on sheet1 or not. Then you can use conditional format based on that formula based column which is telling if the number is present or not.

Based on you sample book, lets say you add a new column in cell F ( to see check the number in column C) and a column in G (to check the number in column D)

so in F2
=IF(C2="","",IF(ISERROR(MATCH(C2,Sheet1!A:A,0)),0,1))
that will check if number in C2 is present in Sheet1

and in G2, you can have
=IF(D2="","",IF(ISERROR(MATCH(D2,Sheet1!A:A,0)),0,1))
that will check if number in D2 is present in Sheet1

Now you can use conditional formatting based on the values on F2 and G2 , to indicate if the number is present or not

For conditional formatting, select Column C and column D
and enter this formula in conditional formatting

=F1=1 and choose the color you want


Hope now it clarifies

Here is the file
http://wikisend.com/download/505070/Book2.xls
Many thanks for your time. It works fine, but just doesnt do what I was hoping, not through your fault just because as you say Excel cant do it!

Cheers
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
well if you dont want to add a helper column, you would need to do it via macro. I think performance will suffer