Home

Conditional Formatting Copy Paste Formula not changing for Rows

Highlighted
JonLee1985
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: 
=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!

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
203 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies