Function to return current Row number

[tylerwelmans]

Hi,

This should be quite simple, any help would be really appreciated.

I have two columns of data and I need a formula which says:

For the column on the left, take cell1 and check the entire column o
the right to see if it occurs. Then take cell2 and check the entir
column on the right again...etc all the way down.

This formula works for the top row, When i replicate down the B colum
value increases as desired, but the range values I am searching throug
also increase. I need them to stay the same.

=COUNTIF((A6:A2000),(B6))

I need to know if there is a function which will return the value o
the current row, this would give me a valid and replicatabl
[side-question..is 'replicatable' a word?] formula.

For example:

=COUNTIF((A6:A2000),(B(Rownumber)))


where 'Rownumber' returns the current row number.

It's only easy if you know the answer....!....any advice would make m
day. Thanks all.

Tyle

 

[Andy B]

Hi

You need to use absolute references rather than relative. Try this:

=COUNTIF(($A$6:$A$2000),(B6))

This tells Excel to always look at the range A6:A2000, no matter where the
formula is pasted.

Andy.

 

[tylerwelmans]

it works! thanks a lot for taking the time to answer.

you the man.

Tyle

 

[A.W.J. Ales]

Tylerwelmans,

Try =Row()

I'm Dutch thus not naticve English, however replicatable sounds "sound
English" to me

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *