Offset Function works in cell, not in named range
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? |
All times are GMT +1. The time now is 03:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com