Excel Conditional Format across sheets
Closed
Gavlar83

Jul 3, 2010 at 10:01 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022  Jul 4, 2010 at 05:40 AM
rizvisa1 Posts 4479 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 conditional formatting if another cell contains specific text ✓  Forum  Excel
 Excel conditional formatting 5 color scale ✓  Forum  Excel
 Excel conditional formatting entire column based on another column ✓  Forum  Excel
 Highlight cell if another cell contains any text ✓  Forum  Excel
 Excel conditional formatting hyperlink ✓  Forum  Excel
1 reply
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
768
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