named range used as argument in ROWS or COLUMNS function results in #VALUE! error

[ask2tsp]

named range namedRng
="$A$1:"&ADDRESS(COUNTA($A:$A);COUNTA(!$1:$1);1)
that in my case evaluates to
$A$1:$H$74

when a cel contains the formula
=ROWS(namedRng)
it shows #VALUE!
i tried evaluate formula where the formula evaluates to ROWS($A$1:$H$74)

when a cel contains the formula
=ROWS($A$1:$H$74)
it shows 74

what is going on here?

 

[CodeNinja]

Using the address function will return a string. You will need to use the indirect function to get this to work in conjunction with this formula.... use =indirect("$A$1:"&ADDRESS(COUNTA($A:$A),COUNTA($1:$1),1)) for your named range.

 

[ask2tsp]

Using the address function will return a string. You will need to use the indirect function to get this to work in conjunction with this formula.... use =indirect("$A$1:"&ADDRESS(COUNTA($A:$A),COUNTA($1:$1),1)) for your named range.

thanks for your explanation. helped a lot