D
DragonslayerApps
The following formula works in a cell:
=COLUMNS(OFFSET(INDIRECT(ADDRESS(MATCH("Capital
Expenditures",Table4!$A:$A),3,1,TRUE)),0,0,1,COUNTA(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?
=COLUMNS(OFFSET(INDIRECT(ADDRESS(MATCH("Capital
Expenditures",Table4!$A:$A),3,1,TRUE)),0,0,1,COUNTA(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?