Change Sheet name in the formula

[darkarahat]

This formula for find duplicate in multiple sheets but i have a problem when changed the name of sheets
=$A$2:$A$1000000
=SUM(COUNTIF(INDIRECT("'Sheet" & ROW($1:$4) & "'!$A:$A"),$A2))>1

That rule i apply on the Home ---> Conditional Formatting ---> New Rule

The problem is when i change sheets name from Sheet1 to Jan, Sheet2 to Feb and so on, the formula not working. I try to changes "'Sheet" to "Jan" or "'Jan" but still not working

Please help me for your advice, thank you

 

[steve the fish]

If you may change them in the future then this maybe better:

=SUM(COUNTIF(INDIRECT(CHOOSE(ROW($A$1:$A$4),"Jan","Feb","Mar","Apr")&"!$A:$A"),$A2))>1

In the row part ROW($A$1:$A$4) the 4 must equal the number of sheets you have.

 

[darkarahat]

If you may change them in the future then this maybe better:

=SUM(COUNTIF(INDIRECT(CHOOSE(ROW($A$1:$A$4),"Jan","Feb","Mar","Apr")&"!$A:$A"),$A2))>1

In the row part ROW($A$1:$A$4) the 4 must equal the number of sheets you have.

Thank you very much, really I am very helpful for this formula