Can't chart dynamic named range??

I have a named range call TMW with this gnarly formula
=OFFSET(INDIRECT(ADDRESS(MATCH("TMW",Clients,0)+1,1)),0,0,IF(COUNTIF(Clients,"TMW")>12,12,COUNTIF(Clients,"TMW")),1)
When I use GOTO and type TMW it selects the correct cells.

Then I have another named range called TMW_ACTIVE with this formula
=OFFSET(TMW,0,2,,)
It simply takes the same range just over two columns. Again when I use
GOTO and type TMW_ACTIVE it selects the correct cells.

The issue is that I have charts that refer to TMW_ACTIVE and when the
named range was hardcoded with values the charts worked but now that I
use the offset formula the chart doesn't work. Any ideas?
THANKS for your help!!

I couldn't get a chart to accept your formula but I did get it to accept a
convensional DNR using the Offset function. The chart doesn't seem to like
DNRs that use the INDIRECT and/or the ADDRESS function. However, with very
minimal testing, I think this may work in lieu of your function. If it's not
quite correct you should be able to fix it.

=OFFSET(Clients,
MATCH("TMW",Clients,0)-1,0,IF(COUNTIF(Clients,"TMW")>12,12,COUNTIF(Clients,"TMW")),1)

Regards,
Greg


"[email protected]" wrote:

> I have a named range call TMW with this gnarly formula
> =OFFSET(INDIRECT(ADDRESS(MATCH("TMW",Clients,0)+1,1)),0,0,IF(COUNTIF(Clients,"TMW")>12,12,COUNTIF(Clients,"TMW")),1)
> When I use GOTO and type TMW it selects the correct cells.
>
> Then I have another named range called TMW_ACTIVE with this formula
> =OFFSET(TMW,0,2,,)
> It simply takes the same range just over two columns. Again when I use
> GOTO and type TMW_ACTIVE it selects the correct cells.
>
> The issue is that I have charts that refer to TMW_ACTIVE and when the
> named range was hardcoded with values the charts worked but now that I
> use the offset formula the chart doesn't work. Any ideas?
> THANKS for your help!!
>
>

how are you referencing the offset formula?

source
=yourworkbook.xls!namedrange

--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
>I have a named range call TMW with this gnarly formula
> =OFFSET(INDIRECT(ADDRESS(MATCH("TMW",Clients,0)+1,1)),0,0,IF(COUNTIF(Clients,"TMW")>12,12,COUNTIF(Clients,"TMW")),1)
> When I use GOTO and type TMW it selects the correct cells.
>
> Then I have another named range called TMW_ACTIVE with this formula
> =OFFSET(TMW,0,2,,)
> It simply takes the same range just over two columns. Again when I use
> GOTO and type TMW_ACTIVE it selects the correct cells.
>
> The issue is that I have charts that refer to TMW_ACTIVE and when the
> named range was hardcoded with values the charts worked but now that I
> use the offset formula the chart doesn't work. Any ideas?
> THANKS for your help!!
>

Actually the chart already exists using a named range (but the named
range is simply hardcoded with a range). So I go into the source data
and replace the named range with the new one and it won't chart.
I did a bunch of experiments last night and as long as the offset
formulas are simple, it works, but the minute I point the named range
to the named range with the complicated offset, it doesn't work. I
haven't tried the offset formula that the second post suggested. I
will give that a try and see if it works.
Don Guillett wrote:
> how are you referencing the offset formula?
>
> source
> =yourworkbook.xls!namedrange
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> <[email protected]> wrote in message
> news:[email protected]...
> >I have a named range call TMW with this gnarly formula
> > =OFFSET(INDIRECT(ADDRESS(MATCH("TMW",Clients,0)+1,1)),0,0,IF(COUNTIF(Clients,"TMW")>12,12,COUNTIF(Clients,"TMW")),1)
> > When I use GOTO and type TMW it selects the correct cells.
> >
> > Then I have another named range called TMW_ACTIVE with this formula
> > =OFFSET(TMW,0,2,,)
> > It simply takes the same range just over two columns. Again when I use
> > GOTO and type TMW_ACTIVE it selects the correct cells.
> >
> > The issue is that I have charts that refer to TMW_ACTIVE and when the
> > named range was hardcoded with values the charts worked but now that I
> > use the offset formula the chart doesn't work. Any ideas?
> > THANKS for your help!!
> >

THANK YOU!!
If I create a named range (TMW) with the offset formula that Greg
suggested, then the second name range uses an offset that refers to
TMW, I can now chart the second named range.
Like Greg said, for some reason it doesn't like the Indirect and
Address functions!! Go figure!!!


Greg Wilson wrote:
> I couldn't get a chart to accept your formula but I did get it to accept a
> convensional DNR using the Offset function. The chart doesn't seem to like
> DNRs that use the INDIRECT and/or the ADDRESS function. However, with very
> minimal testing, I think this may work in lieu of your function. If it's not
> quite correct you should be able to fix it.
>
> =OFFSET(Clients,
> MATCH("TMW",Clients,0)-1,0,IF(COUNTIF(Clients,"TMW")>12,12,COUNTIF(Clients,"TMW")),1)
>
> Regards,
> Greg
>
>
> "[email protected]" wrote:
>
> > I have a named range call TMW with this gnarly formula
> > =OFFSET(INDIRECT(ADDRESS(MATCH("TMW",Clients,0)+1,1)),0,0,IF(COUNTIF(Clients,"TMW")>12,12,COUNTIF(Clients,"TMW")),1)
> > When I use GOTO and type TMW it selects the correct cells.
> >
> > Then I have another named range called TMW_ACTIVE with this formula
> > =OFFSET(TMW,0,2,,)
> > It simply takes the same range just over two columns. Again when I use
> > GOTO and type TMW_ACTIVE it selects the correct cells.
> >
> > The issue is that I have charts that refer to TMW_ACTIVE and when the
> > named range was hardcoded with values the charts worked but now that I
> > use the offset formula the chart doesn't work. Any ideas?
> > THANKS for your help!!
> >
> >