=COUNTIF(INDIRECT("Sheet1!$W$1:$W$"&M1),B7)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Joseph" wrote in message
...
Hi,

I have a small prob. I have a formula where the cell co-ordinates will
have
to change based on the value available in another cell. For eg.,

If formula is

=COUNTIF(Sheet1!$W$1:$W$500,B7)

I want the number 500 to be picked from another cell which contains the
exact number or rows.

How can I achieve this?

Thanks in advance.

- Joseph