Highlight Duplicates Across Multiple Sheets

Highlight Duplicates Across Multiple Sheets

QUESTION

Hi, I have a workbook with 20 tabs, for simplicity sake I will name them 1 to 20. Tab 2 to 20 are data entry tabs while tab 1 is the masterlist tab where all data from tabs 2 to 20 are consolidated. how do I use the highlight duplicate conditional format to check for duplicates within and across worksheets (2 to 20)?

SOLUTION

First, Created dynamic range name by formulas:

=OFFSET(Admin!$A$2;0;0;COUNTA(Admin!$A:$A)-1;1)

under the name “sht”.

Put tabs names in this Sheet named (“Admin”).

Use the below formula in conditional formatting of each Tab:

=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&sht&”‘!B2:F15″);B2))>1

The soltion file attached:

highlight duplicates across multiple worksheets

You may also like...

Bir Cevap Yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir

Şu HTML etiketlerini ve özelliklerini kullanabilirsiniz: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>