Conditional Formatting Copy Paste Formula not changing for Rows

Occasional Visitor

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: 
(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: 
(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!

Related Conversations