Conditional Formatting Copy Paste Formula not changing for Rows

**JonLee1985** · 12-03-2018, 02:42 AM

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!

**Pepe Le Mokko** · 12-03-2018, 03:13 AM

Rule 03: Cross-posting Without Telling Us

Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question. If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

If you have less than 10 posts, do not try to copy and paste the link. Instead, type the link out in your thread.

No further help to be offered, please, until the OP has complied with this request.

**FDibbins** · 12-03-2018, 03:17 AM

@ Pepe, OP has less than 10 posts, so they cannot include a link yet

Jon, please type out the link to the other site you posted this in. Leave gaps or add ... or anything to break it up.
(or Pepe could post it for you.)

**JonLee1985** · 12-03-2018, 04:17 AM

Thanks for the assistance FDibbins

I can't type anything similar to the other page or thread as it raises an error... the other post is on MrExcel

**JonLee1985** · 12-03-2018, 04:18 AM

Other site I posted was MrExcel using the same Subject title.

A User there offered solution of removing the INDIRECT function, which corrected the copy/paste requirement I have.
But it now means only 2nd or 3rd etc. duplicate is highlighted, and as the purpose of the conditional formatting was to have each individual sheet editor aware of duplicates, this solution removes the goal that the conditional formatting was trying to achieve.