How to use a pointer in an IF Statement?

[engel59]

Here is what I'm using right now:
=IF($H2=0, 0,COUNTIF(H$2:H$1116,TRIM($H2)))

What this does is look for identical words in column H rows 2 thru 1116 and
changes the cell value to 1 if a match is found. I them use Conditional
Formating to show the cell as green if no match and red if there is a match.

I have three columns that have part numbers in them and I wanted to know if
there was a way I could check the other rows all at the same time?

The other solution that would help would be to be able and change just the
"H" in the formula. How could I do something like that?

cell A1 = H

=IF($"=A1"2=0, 0,COUNTIF("=A1"$2:"=A1"$1116,TRIM($"=A1"2)))

How can I make this work?

 

[Bob Phillips]

INDIRECT(A1&"2")

 

[engel59]

Bob,
Thank you for your responce.

I tried:
=IF(INDIRECT($A$1&"2",1)=0,
0,COUNTIF(INDIRECT($A$1&"2"):INDIRECT($A$1&"1116"),TRIM(INDIRECT($A$1&"2"))))

The error I get is "Volatle"

I tried doing a search but I can't figure it out what Volatle means in the
script.

 

[engel59]

Also the first and last part of the formula needs to match the row is was
copied to.
The "2" locks that number in.

If I could get this section to work
[(INDIRECT($A$1&"2"):INDIRECT($A$1&"1116")] that would work out since those
numbers are a constant anyway.

 

[Bob Phillips]

=IF(INDIRECT(A1&ROW())=0,0,COUNTIF(INDIRECT(A1&ROW()&":"&A1&116),TRIM(INDIRECT(A1&ROW()))))


--
__________________________________
HTH

Bob

Also the first and last part of the formula needs to match the row is was
copied to.
The "2" locks that number in.

If I could get this section to work
[(INDIRECT($A$1&"2"):INDIRECT($A$1&"1116")] that would work out since
those
numbers are a constant anyway.

Bob,
Thank you for your responce.

I tried:
=IF(INDIRECT($A$1&"2")=0,
0,COUNTIF(INDIRECT($A$1&"2"):INDIRECT($A$1&"1116"),TRIM(INDIRECT($A$1&"2"))))

The error I get is "Volatle"

I tried doing a search but I can't figure it out what Volatle means in
the
script.