Excel2000: Dynamic named range from area with formulas

[Arvi Laanemets]

Hi

I have a worksheet p.e. Employees with links in it, like
=IF('T:\[Registers.xls]Personal'!A1="","",'T:\[Registers.xls]Personal'!A1)

The range with formulas is A1:G999, the range with returned nonempty values
~A1:G500 at moment (A1:G1 are headers from Personal sheet of Registers.xls).
The nonempty range is always continuous (the source table is an ODBC query
result table)

I need to define the named range in such a way, that only rows with nonempty
values are returned. The usual way to define dynamic named range
=OFFSET(Employees!$A$2,,,COUNTIF(Employees!$A:$A,"<>")-1,7)
does return the range A2:G999, irrespective of range with real data
returned. It seems that all cells with formulas are counted as non-empty.
Has somebody a better idea?

Thanks in advance

 

[Arvi Laanemets]

Hi

I did find the solution myself.
=OFFSET(Employees!$A$2,,,COUNTIF(Employees!$A:$A,">""")-1,7)
when values in column A are strings, and
=OFFSET(Employees!$A$2,,,COUNTIF(Employees!$A:$A,">0"),7)
when values in column A are numeric.