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
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022  Jul 4, 2010 at 05:40 AM
Related:
 Excel Conditional Format across sheets
 Excel date format dd.mm.yyyy  Guide
 Format factory  Download  Other
 Marksheet format in excel  Guide
 Samsung format code  Guide
 How to screenshot excel sheet  Guide
1 response
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jul 3, 2010 at 10:11 AM
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
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
Jul 3, 2010 at 10:30 AM
Jul 3, 2010 at 10:33 AM
Jul 3, 2010 at 10:39 AM
Jul 3, 2010 at 10:43 AM
In that case you only need this on your nonmaster 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
Jul 3, 2010 at 10:51 AM
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