if i enter data into enter data into b5:b25

then i fix your formula:
=OFFSET(sheet1!$B$5,0,0,count(sheet1!$B:$B),5)

and then type this in the immedidate window:
?range("myprod").Address

it returns:
$B$5:$F$25


--


Gary


"Rob" wrote in message
...
Hi,

I have a dynamic range name set as MyProd, I used
=OFFSET((sheet1!$B$5,0,0,count(sheet1!$B:$B),5) in the Names menu to define
this. However, since setting this up, the Macro I had that looked up data in
a column from the range MyProd, returns errors as if the range address isn't
known. Is there as solution to resolve this eg. somehow define the address
before running the macro?

Hoping someone can give some direction.

Thanks, Rob