Previous Topic Next Topic

Excel Conditional Format across sheets

Closed
Gavlar83 - Jul 3, 2010 at 10:01 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jul 4, 2010 at 05:40 AM
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!
Related:

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 3, 2010 at 10:11 AM
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
0
Gavlar83
Jul 3, 2010 at 10:30 AM
Ok thanks for the reply. I think I am understanding it, no actually I am not! So where would I put that formula? I have numbers in column C on sheet 2 and Column A on sheet 1. Would I put this formula in an empty cell on each sheet? Confused!
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 3, 2010 at 10:33 AM
Since you say that number can be in any sheet and for each number in any sheet you want to find out if it was repeated or not. Because of these reasons you would be putting in a column on each sheet. Of course this for only for a2. So lets say you put this formula in F2, then drag the formula till the last row of the sheet
0
Gavlar83
Jul 3, 2010 at 10:39 AM
Sorry I am struggling to understand what you are saying. Not sure if I made my original question clear. I have a list of numbers in sheet 1, over time I shall be adding a list of numbers to sheet 2, then a new list to sheet 3, then 4 etc. Sheet1 will always be my master. What I wish is to look through the sheets 2 onwards and find highlighted cells which tells me that that number is on my master sheet (sheet1).
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 3, 2010 at 10:43 AM
Ohhhhhhhhhhhhhhhhhhhhhhhhhhhhh

In that case you only need this on your non-master sheets. In this formula, Sheet1 is the name of your master sheet.

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

This formula is saying that look at the value in Cell A2, and see if that number exists on my master sheet (sheet1) in column A. If it is present then show me 1 . If it is not present than show me a 0
0
Gavlar83
Jul 3, 2010 at 10:51 AM
Sorry! My fault for not being clear!

Ive tried that formula but not working. Few things I dont understand...
1) I have titles in A1, B1, C1, D1 on sheets2 onwards, the data is then in the cells underneath. So what cell do I put the formula in?
2) Is there a way to put the formula in a cell but ensuring that my data is still displayed in that cell?
3) This is hard explaining through words! lol But thank you
0

Similar discussions

Highlight cell if another cell contains specific text
Abbi -
vcoolio -

1 response
How to use conditional format based on text?
Leanne99 -
Mooseman -

26 responses