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]

See if this works:

=SUM(COUNTIF(INDIRECT(TEXT(DATE(1900,ROWS($A$1:A1),1),"mmm")&"!$A:$A"),$A2))>1

 

[darkarahat]

See if this works:

=SUM(COUNTIF(INDIRECT(TEXT(DATE(1900,ROWS($A$1:A1),1),"mmm")&"!$A:$A"),$A2))>1

Sorry Steve isn't working

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Phone[/TD]
[TD]Home[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]123[/TD]
[TD]789[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]234[/TD]
[TD]678[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]123[/TD]
[TD]789[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]456[/TD]
[TD]567[/TD]
[/TR]
</tbody>[/TABLE]

With use my formula it's working and show with highlight when duplicated in any sheets but when i changed Sheet name suddenly my formula isn't working and i know the problem is =SUM(COUNTIF(INDIRECT("'Sheet" & ROW($1:$4) & "'!$A:$A"),$A2))>1 so i want rename my sheet what i want like Jan, Feb, Mar

 

[steve the fish]

In sheet called 'Jan' place 999 in cell A1. What does this produce:

=INDIRECT(TEXT(DATE(1900,ROWS($A$1:A1),1),"mmm")&"!A1")

 

[darkarahat]

I upload for more clear

https://prnt.sc/mjz4t8

Blue Box (Sheet1...Sheet4) that i want to change with any name

Thanks

 

[darkarahat]

In sheet called 'Jan' place 999 in cell A1. What does this produce:

=INDIRECT(TEXT(DATE(1900,ROWS($A$1:A1),1),"mmm")&"!A1")

https://prnt.sc/mjzbr9

 

[steve the fish]

Ok make your Sheets called Jan, Feb, Mar, Apr then

=SUM(COUNTIF(INDIRECT(TEXT(DATE(1900,ROW(A1:A4),1),"mmm")&"!$A:$A"),$A2))>1

 

[darkarahat]

Ok make your Sheets called Jan, Feb, Mar, Apr then

=SUM(COUNTIF(INDIRECT(TEXT(DATE(1900,ROW(A1:A4),1),"mmm")&"!$A:$A"),$A2))>1

no highlight when it duplicated

 

[steve the fish]

Missed some absolution of the cells:

=SUM(COUNTIF(INDIRECT(TEXT(DATE(1900,ROW($A$1:$A$4),1),"mmm")&"!$A:$A"),$A2))>1

 

[darkarahat]

Missed some absolution of the cells:

=SUM(COUNTIF(INDIRECT(TEXT(DATE(1900,ROW($A$1:$A$4),1),"mmm")&"!$A:$A"),$A2))>1

it's working perfectly, thank you very much. Can you explain your formula ? if I want to change with another name