Keeping Cell Location the Same

[Brian Bagnall]

I made a formular that looks like this:

=IF(COUNTIF(A2:A6,A2)>1,"Duplicate","")

Now what I want to do is copy and paste it down the whole row. I want
the last A2 (,A2) to increment with each row. e.g. The next one would
say:

=IF(COUNTIF(A2:A6,A2)>1,"Duplicate","")

But I want A2:A6 to remain static. Isn't there a symbol I put before
the A2:A6 to keep it from changing?

Thanks,

Brian

 

[Bernie Deitrick]

Brian,

=IF(COUNTIF($A$2:$A$6,A2)>1,"Duplicate","")

HTH,
Bernie
MS Excel MVP

 

[RagDyer]

I don't see any difference between your 2 formulas.

However, what you want is the dollar sign ($).
That changes relative references to absolute references,
Which means the absolutes *don't* change when copied.

Place it before a column and/or row reference.
A1
$A1
$A$1
A$1

You can select a reference in the formula bar and then hit <F4> to change
it.
Each hit of <F4> will shift the references between row and column in a loop.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================