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!