The following formula works in a cell:

=COLUMNS(OFFSET(INDIRECT(ADDRESS(MATCH("Capital
Expenditures",Table4!$A:$A),3,1,TRUE)),0,0,1,COUNT A(Table4!$C$4:$DV$4)))

It returns the same value as COUNTA(Table4!$C$4:$DV$4), which is the number
of colums in the resulting range. It verifies that the full OFFSET formula
works.

If I place that OFFSET formula into a Named Range and base a chart series or
function on that named range, I get a #Ref error. So the following formula
returns an error:

=COLUMNS(Worksheet!NamedRange)

When NamedRange is a Named Range equal to the OFFSET formula in the first
example.

A somewhat less complex OFFSET formula placed into a named range works just
fine. It looks like this:

=OFFSET(Table4!$C$4,0,2,1,COUNTA(Table4!$C$4:$DV$4 ))

The difference between the two offset formulas is that one has a fixed Base
reference, the other calculates the base reference using
Indirect(Address(match())) formulas.

What limitation am I running up against here, and how can I work around it?