Gday All
Goal:- Trying to create a conditional format that goes across three or four sheets which are updated monthly for rosters, formula written so that if a duplicate (name) is detected in the relevant cells in any row on any sheet it will highlight the cell in red and bold to identify there's a clash.
Issue:- The formula and conditional format seems to be working for a row across the sheets. But when I copy and paste in any way, only half the formula updates for the new row. Looking for a solution so that the formula updates for any row it's copied to, or another way to input the conditional formatting so that there's no issue.
Further Detail:-
Sheet 1-3, Row 2-3 Month and Column Title, Column A-B Date and Location (all matching)
Sheet 1, Row 4-16, Column C-E (are three choices)
Sheet 2, Row 4-16, Column C-H (are six choices)
Sheet 3, Row 4-16, Column C-H (are six choices)
Sheet 4, Each Column is a name list that populates any of the choices on the other sheets by drop down menu.
Sheet 1, C4-E4, has three conditions applying to all three cells:
=COUNTIF(C4:E4,C4)>1
=COUNTIF(INDIRECT("'SHEET2'!C4:H4"),C4)>0
=COUNTIF(INDIRECT("'SHEET3'!C4:H4"),C4)>0
(This is repeated to Sheet 2 and 3 for C4-H4 with correct changes to INDIRECT formula.)
When we copy and paste to Row 5 we end up with the below:
Sheet 1, C5-E5:
=COUNTIF(C5:E5,C5)>1
=COUNTIF(INDIRECT("'SHEET2'!C4:H4"),C5)>0
=COUNTIF(INDIRECT("'SHEET3'!C4:H4"),C5)>0
(The INDIRECT formula inside "" doesn't change Row to Row 5, while all others outside of "" do change.)
Hope that's enough information for someone to help us out!
