Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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(Cl ients,"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!! |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Can't chart dynamic named range??
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(Client s,"TMW")),1) Regards, Greg " 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(Cl ients,"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!! |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Can't chart dynamic named range??
how are you referencing the offset formula?
source =yourworkbook.xls!namedrange -- Don Guillett SalesAid Software wrote in message oups.com... 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(Cl ients,"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!! |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Can't chart dynamic named range??
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 wrote in message oups.com... 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(Cl ients,"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!! |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Can't chart dynamic named range??
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(Client s,"TMW")),1) Regards, Greg " 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(Cl ients,"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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic column chart - auto sort on data range | Charts and Charting in Excel | |||
Dynamic chart that displays a range starting today | Charts and Charting in Excel | |||
Dynamic Step Chart using range names | Charts and Charting in Excel | |||
Dynamic Named Range with blank cells | Excel Discussion (Misc queries) | |||
Define a range based on another named range | Excel Worksheet Functions |