can I use Indirect function within range definitons for names?

[Guest]

I am trying to define a name relating to an area whose boundaries will vary
depending upon the data contained within it. One cell (which does not reside
within the area) contains the cell reference of the second boundary point, so
I tried using the following definition of the area
"=Sheet1!$H$58:INDIRECT(Sheet1!$I$53)" -typically I53 contains:"H68". If I
use F5 (goto) there is no problem going to this new range, if however I use a
function such as "=COUNTIF(newarea,H61)" -where "newarea" is the name defined
with area above it crashes Excel -this is in both 2003 and 2007 versions.

Any ideas?

 

[Bernard Liengme]

You need to have INDIRECT generate the actual range
=INDIRECT("Sheet1!$H$58:"&$I$53)
best wishes