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
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies