can I use Indirect function within range definitons for names?
 

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?

can I use Indirect function within range definitons for names?
 

You need to have INDIRECT generate the actual range
=INDIRECT("Sheet1!$H$58:"&$I$53)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"surreyjed" wrote in message
...
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?