Dragging Down formula for conditional formatting

Hi,

I’m going to preface this with that I’m quite new to excel formulas so please bear with with me...

I’m trying to create a master roster file which has multiple rosters in it (one roster per sheet). The purpose of this file is that certain rosters are run concurrently in a day so I wanted to add conditional formatting where if a person’s name appears on the same day on two different rosters their name is highlighted with the cell colour matching the sheet colour which they are duplicated in. So in essence a duplicate check. After some searching online I found this formula which seems to work:

=MATCH(C6,INDIRECT("Olive!D4:E4"),0)

I’ve conditional formatted this formula to apply to cells C6:J6 in the sheet I want. But I need to apply this to multiple rows so when I drag down the cells that formula updates to:

=MATCH(C7,INDIRECT("Olive!D4:E4"),0) while I need it to update to =MATCH(C7,INDIRECT("Olive!D5:E5"),0). Just wondering if it’s possible for a formula in conditional formatting to be able to update it’s reference cells in another sheet when I drag down?

Otherwise is there a formula that I can apply to a whole table where it looks at each row or row corresponding to a date and cross references to a row in another sheet or the row corresponding to a date in another sheet and highlights the duplicates? I tried the below and it didn’t work

=MATCH(C6,INDIRECT("Olive!D4:E16"),0) to be applied to cells C6:J16

Hope this question makes sense and thanks in advance!

Hi mehmetcik,

Thanks for the quick reply!

Editing the range that the conditional formatting applies to does work but it makes the range only look at the reference cells D4:E4 in the sheet Olive. Eg if I set the range from C6:J6 to C6:J18, it will show be duplicates found in C6:J18 when referencing to D4:E4. But I need the roster to look up row by row. ie:
C6:J6 looks at duplicates in D4:E4 in Olive sheet
C7:J7 looks at duplicates in D5:E5 in Olive sheet
C8:J8 looks at duplicates in D6:E6 in Olive sheet
Etc...

So is there a way to adjust it so the conditional formatting does that?

Thanks!

Why are you using INDIRECT in that formula?

Pete

Hi Pete,

To be brutally honest I’m not entirely sure, it was the result of my googling and said it was needed since what I’m working with is text and not numbers

I’ve attached a screen shot of an example of the page I’m working on - currently it’s set for C6:J6 if the name appears in I6:M6 it will highlight the cell green as shown. I was looking at having it apply multiple conditional formattings:
1. To look at it row by row ie C7:J7 looks at I7:Attachment 658208M7, C8:J8 looks at I8:M8 within the same sheet and
2. To do the above but also for another sheet as I mentioned above

In my mind I think it should be the same formula for both conditions, but that’s my extremely limited excel knowledge
Attachment 658208

Both attachment links come up as invalid when I click on them. You might have tried to use the Paperclip icon, which has not worked on this forum for many years (and the Tech Team don't seem to be willing/able to fix it).

Instead, you should follow the instructions given in the yellow banner at the top of the screen, telling you how to attach a file to one of your posts.

Pete

Apologies Pete, I’ve tried to reattach the file now, let me know if it doesn’t work!

Thanks!

Sorry everyone! Still new to this forum... I’ve attached the file now

Thanks for everyone’s input and trying to help so far!

Thanks for the quick reply AliGW!

I’ve done what you said but I do need each row to look up it’s corresponding row instead of just matching the whole table to I6:M6 - so I want it to highlight any names in C6:F6 that is duplicated in I6:M6 while for C7:F7 to highlight duplicates found in I7:M7, C8:F8 in I8:M8 etc... which is why I had all those rules per row as I couldn’t think of another way, but I also don’t want to have to do it row by row as it’s for a whole year and I have 8 rosters to go through...

Hope my query makes sense! (And is possible....)

Thanks for the quick reply AliGW!

So I’m trying to create a master roster file which has multiple rosters in it (one roster per sheet). I want to create a CF rule as certain rosters are run concurrently in a day so I wanted the CF rule to show if a person’s name appears on the same day on two different rosters their name is highlighted with the cell colour matching the sheet colour which they are duplicated in. So in essence a duplicate check.

I didn’t know how to do this so I did some googling and came across the below formula (I wanted to post a link to the website that had the below formula but I haven’t posted enough to be allowed to put a link in my reply yet)

=MATCH(C6,INDIRECT("Olive!D4:E4"),0)

So in the CF rule I would like selected cells in a row in the table (say C6:J6) to look up the cells in another row (say I6:M6) and if there are duplicates then the duplicated name is highlighted in C6:F6. At the same time I would like the row (C6:J6) to look up another row in another see (Say Sheet Olive, D4:34) and if there is a duplicate in names then it highlights in C6:F6 as well. So each row needs to look up another row but they are in succession eg C6:J6 - I6:M6, C7:J7 - I7:M7, C8:J8 - I8:M8 etc...

The above rule did work but I could not apply it on a mass scale as the CF rule would not update the cells in the rule when I dragged down/used format painter. But I need each row to look up another row as each row represents a new week in each roster. So since I the dragging didn’t work, it’s leading me to think that I’m probably not using the best formula and hence posting my conundrum in this forum to seek the help of those, like yourself, who are much better at excel than I am.

Hope this clears things up and makes sense as to what I’m trying to achieve.

Thanks in advance!

Oh my goodness JeteMc! Everything works amazingly, what a perfect solution! Thank you so much!! Been boggling my mind of weeks, thanks for all your time and effort in helping me!