Excel Conditional Format across sheets
[Closed]
Report
Gavlar83

rizvisa1
rizvisa1
 Posts
 4476
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 August 2, 2020
Related:
 Excel Conditional Format across sheets
 Excel conditional formatting 5 color scale ✓  Forum  Excel
 Excel conditional formatting if cell contains any date  HowTo  Excel
 Excel conditional formatting entire column based on another column ✓  Forum  Excel
 Excel conditional formatting if another cell contains any text ✓  Forum  Excel
 Excel conditional formatting if another cell contains specific text ✓  Forum  Excel
1 reply
rizvisa1
 Posts
 4476
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 August 2, 2020
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
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
=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 reexplain your problem too
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
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
Cheers