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!