I've recently realized that Excel 2007 has a bug that is preventing me from
using dynamic Named ranges to create dynamic charts. Several people have
noted on this newsgroup that they've experienced similar problems with Excel
2007 charting using named ranges. In my case i get the error message "A
formula in this worksheet contains one or more invalid references." When I
open this same workbook at home (using Excel 2002), everything works fine.
SP1 addressed something similar to the issue I'm having, but in my case SP1
did not fix the problem. As a result I'm wondering if there are any
alternatives to using Named ranges? This is going to be a template accessed
by multiple users from the web. As a result, it needs to be compatable with
older versions of Excel and not use VBA.

Does anyone have any workarounds, or am I out of luck? The Series ranges
could expand or shrink.

Jeff

You can get some dynamic charting in Excel 2003 if you base the chart on
data in a List, or in 2007 if you base it on a Table. However, you mention
2002, which lacks this capability.

What are your name definitions and series formulas (in 2002). There may be
alternative ways to define the names.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"goofy11" wrote in message
...
I've recently realized that Excel 2007 has a bug that is preventing me
from
using dynamic Named ranges to create dynamic charts. Several people have
noted on this newsgroup that they've experienced similar problems with
Excel
2007 charting using named ranges. In my case i get the error message "A
formula in this worksheet contains one or more invalid references." When
I
open this same workbook at home (using Excel 2002), everything works fine.
SP1 addressed something similar to the issue I'm having, but in my case
SP1
did not fix the problem. As a result I'm wondering if there are any
alternatives to using Named ranges? This is going to be a template
accessed
by multiple users from the web. As a result, it needs to be compatable
with
older versions of Excel and not use VBA.

Does anyone have any workarounds, or am I out of luck? The Series ranges
could expand or shrink.

Jeff

Thanks for the reply Jon. I don't have access to my home computer now, but I
was able to open this at work using Excel 2003. These charts work just fine
using Excel 2003. Here is the info you asked for (but from Excel 2003).

MY 3 NAMED RANGES:
Dates =OFFSET('worksheet_name'!$A$2,0,0,COUNT('worksheet _name'!$A:$A),1)
POSQty =OFFSET('workbook_name.xls'!Dates,0,1)
Avg_Price =OFFSET('workbook_name.xls'!Dates,0,2)

HERE ARE THE 2 SERIES FORMULAS
=SERIES('worksheet_name'!$B$1,'workbook_name.xls'! Dates,'workbook_name.xls'!POSQty,2)
=SERIES('worksheet_name'!$C$1,'workbook_name.xls'! Dates,'workbook_name.xls'!Avg_Price,1)

The List option might be a possibility (at least it would work in the 2 most
recent versions). If I were to do that, would I need to set it up using
Excel 2003?

Jeff



"Jon Peltier" wrote:

You can get some dynamic charting in Excel 2003 if you base the chart on
data in a List, or in 2007 if you base it on a Table. However, you mention
2002, which lacks this capability.

What are your name definitions and series formulas (in 2002). There may be
alternative ways to define the names.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"goofy11" wrote in message
...
I've recently realized that Excel 2007 has a bug that is preventing me
from
using dynamic Named ranges to create dynamic charts. Several people have
noted on this newsgroup that they've experienced similar problems with
Excel
2007 charting using named ranges. In my case i get the error message "A
formula in this worksheet contains one or more invalid references." When
I
open this same workbook at home (using Excel 2002), everything works fine.
SP1 addressed something similar to the issue I'm having, but in my case
SP1
did not fix the problem. As a result I'm wondering if there are any
alternatives to using Named ranges? This is going to be a template
accessed
by multiple users from the web. As a result, it needs to be compatable
with
older versions of Excel and not use VBA.

Does anyone have any workarounds, or am I out of luck? The Series ranges
could expand or shrink.

Jeff

I know SP1 fixed the one specific issue I'd been tracking, but earlier in
the year I'd run across a whole family of related problems, and I haven't
had a chance to follow-up.

One thing you could try is to scope the names for the worksheet, not the
whole workbook. This means, in pre-2007 typing the sheet name and
exclamation point in front of the name when naming the name (sounds stupid,
but the official name for "named ranges" is "names). In 2007, when defining
the name, choose the sheet name from the appropriate dropdown (I forget what
it's labeled, but it should be obvious). Then the series formula would have
the sheet name, not the workbook name, in the references:

=SERIES('worksheet_name'!$B$1,'worksheet_name'!Dat es,'worksheet_name'!POSQty,2)

This was a successful workaround in the case that SP1 eventually fixed. Of
course, sheet level names in series formulas have other issues, which are
different in 2007 and in 2003.

I'm not sure how lists and tables translate between 2003 and 2007. I would
think it would be more successful to set it up in 2003 then use it in both
versions, though the reverse may actually work. Let me put that on my long
list of things to try in the short times I have available!

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"goofy11" wrote in message
...
Thanks for the reply Jon. I don't have access to my home computer now,
but I
was able to open this at work using Excel 2003. These charts work just
fine
using Excel 2003. Here is the info you asked for (but from Excel 2003).

MY 3 NAMED RANGES:
Dates =OFFSET('worksheet_name'!$A$2,0,0,COUNT('worksheet _name'!$A:$A),1)
POSQty =OFFSET('workbook_name.xls'!Dates,0,1)
Avg_Price =OFFSET('workbook_name.xls'!Dates,0,2)

HERE ARE THE 2 SERIES FORMULAS
=SERIES('worksheet_name'!$B$1,'workbook_name.xls'! Dates,'workbook_name.xls'!POSQty,2)
=SERIES('worksheet_name'!$C$1,'workbook_name.xls'! Dates,'workbook_name.xls'!Avg_Price,1)

The List option might be a possibility (at least it would work in the 2
most
recent versions). If I were to do that, would I need to set it up using
Excel 2003?

Jeff



"Jon Peltier" wrote:

You can get some dynamic charting in Excel 2003 if you base the chart on
data in a List, or in 2007 if you base it on a Table. However, you
mention
2002, which lacks this capability.

What are your name definitions and series formulas (in 2002). There may
be
alternative ways to define the names.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"goofy11" wrote in message
...
I've recently realized that Excel 2007 has a bug that is preventing me
from
using dynamic Named ranges to create dynamic charts. Several people
have
noted on this newsgroup that they've experienced similar problems with
Excel
2007 charting using named ranges. In my case i get the error message
"A
formula in this worksheet contains one or more invalid references."
When
I
open this same workbook at home (using Excel 2002), everything works
fine.
SP1 addressed something similar to the issue I'm having, but in my case
SP1
did not fix the problem. As a result I'm wondering if there are any
alternatives to using Named ranges? This is going to be a template
accessed
by multiple users from the web. As a result, it needs to be compatable
with
older versions of Excel and not use VBA.

Does anyone have any workarounds, or am I out of luck? The Series
ranges
could expand or shrink.

Jeff

Jon,

Thanks for the additional ideas. I tried scoping the names for the
worksheet rather than the workbook, but alas, I'm getting the same error. My
Series names are now in the format you illustrated below (with worksheet name
instead of workbook). When I first build the chart from scratch, everything
seems to work okay. But when I save, close, and then reopen the workbook, I
immediately get the same error message and my chart is messed up.

I'll give the List approach a whirl.

Jeff

"Jon Peltier" wrote:

I know SP1 fixed the one specific issue I'd been tracking, but earlier in
the year I'd run across a whole family of related problems, and I haven't
had a chance to follow-up.

One thing you could try is to scope the names for the worksheet, not the
whole workbook. This means, in pre-2007 typing the sheet name and
exclamation point in front of the name when naming the name (sounds stupid,
but the official name for "named ranges" is "names). In 2007, when defining
the name, choose the sheet name from the appropriate dropdown (I forget what
it's labeled, but it should be obvious). Then the series formula would have
the sheet name, not the workbook name, in the references:

=SERIES('worksheet_name'!$B$1,'worksheet_name'!Dat es,'worksheet_name'!POSQty,2)

This was a successful workaround in the case that SP1 eventually fixed. Of
course, sheet level names in series formulas have other issues, which are
different in 2007 and in 2003.

I'm not sure how lists and tables translate between 2003 and 2007. I would
think it would be more successful to set it up in 2003 then use it in both
versions, though the reverse may actually work. Let me put that on my long
list of things to try in the short times I have available!

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"goofy11" wrote in message
...
Thanks for the reply Jon. I don't have access to my home computer now,
but I
was able to open this at work using Excel 2003. These charts work just
fine
using Excel 2003. Here is the info you asked for (but from Excel 2003).

MY 3 NAMED RANGES:
Dates =OFFSET('worksheet_name'!$A$2,0,0,COUNT('worksheet _name'!$A:$A),1)
POSQty =OFFSET('workbook_name.xls'!Dates,0,1)
Avg_Price =OFFSET('workbook_name.xls'!Dates,0,2)

HERE ARE THE 2 SERIES FORMULAS
=SERIES('worksheet_name'!$B$1,'workbook_name.xls'! Dates,'workbook_name.xls'!POSQty,2)
=SERIES('worksheet_name'!$C$1,'workbook_name.xls'! Dates,'workbook_name.xls'!Avg_Price,1)

The List option might be a possibility (at least it would work in the 2
most
recent versions). If I were to do that, would I need to set it up using
Excel 2003?

Jeff



"Jon Peltier" wrote:

You can get some dynamic charting in Excel 2003 if you base the chart on
data in a List, or in 2007 if you base it on a Table. However, you
mention
2002, which lacks this capability.

What are your name definitions and series formulas (in 2002). There may
be
alternative ways to define the names.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"goofy11" wrote in message
...
I've recently realized that Excel 2007 has a bug that is preventing me
from
using dynamic Named ranges to create dynamic charts. Several people
have
noted on this newsgroup that they've experienced similar problems with
Excel
2007 charting using named ranges. In my case i get the error message
"A
formula in this worksheet contains one or more invalid references."
When
I
open this same workbook at home (using Excel 2002), everything works
fine.
SP1 addressed something similar to the issue I'm having, but in my case
SP1
did not fix the problem. As a result I'm wondering if there are any
alternatives to using Named ranges? This is going to be a template
accessed
by multiple users from the web. As a result, it needs to be compatable
with
older versions of Excel and not use VBA.

Does anyone have any workarounds, or am I out of luck? The Series
ranges
could expand or shrink.

Jeff

Try a test with a few names in a new workbook, and see if it works any
better.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"goofy11" wrote in message
...
Jon,

Thanks for the additional ideas. I tried scoping the names for the
worksheet rather than the workbook, but alas, I'm getting the same error.
My
Series names are now in the format you illustrated below (with worksheet
name
instead of workbook). When I first build the chart from scratch,
everything
seems to work okay. But when I save, close, and then reopen the workbook,
I
immediately get the same error message and my chart is messed up.

I'll give the List approach a whirl.

Jeff

"Jon Peltier" wrote:

I know SP1 fixed the one specific issue I'd been tracking, but earlier in
the year I'd run across a whole family of related problems, and I haven't
had a chance to follow-up.

One thing you could try is to scope the names for the worksheet, not the
whole workbook. This means, in pre-2007 typing the sheet name and
exclamation point in front of the name when naming the name (sounds
stupid,
but the official name for "named ranges" is "names). In 2007, when
defining
the name, choose the sheet name from the appropriate dropdown (I forget
what
it's labeled, but it should be obvious). Then the series formula would
have
the sheet name, not the workbook name, in the references:

=SERIES('worksheet_name'!$B$1,'worksheet_name'!Dat es,'worksheet_name'!POSQty,2)

This was a successful workaround in the case that SP1 eventually fixed.
Of
course, sheet level names in series formulas have other issues, which are
different in 2007 and in 2003.

I'm not sure how lists and tables translate between 2003 and 2007. I
would
think it would be more successful to set it up in 2003 then use it in
both
versions, though the reverse may actually work. Let me put that on my
long
list of things to try in the short times I have available!

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"goofy11" wrote in message
...
Thanks for the reply Jon. I don't have access to my home computer now,
but I
was able to open this at work using Excel 2003. These charts work just
fine
using Excel 2003. Here is the info you asked for (but from Excel
2003).

MY 3 NAMED RANGES:
Dates
=OFFSET('worksheet_name'!$A$2,0,0,COUNT('worksheet _name'!$A:$A),1)
POSQty =OFFSET('workbook_name.xls'!Dates,0,1)
Avg_Price =OFFSET('workbook_name.xls'!Dates,0,2)

HERE ARE THE 2 SERIES FORMULAS
=SERIES('worksheet_name'!$B$1,'workbook_name.xls'! Dates,'workbook_name.xls'!POSQty,2)
=SERIES('worksheet_name'!$C$1,'workbook_name.xls'! Dates,'workbook_name.xls'!Avg_Price,1)

The List option might be a possibility (at least it would work in the 2
most
recent versions). If I were to do that, would I need to set it up
using
Excel 2003?

Jeff



"Jon Peltier" wrote:

You can get some dynamic charting in Excel 2003 if you base the chart
on
data in a List, or in 2007 if you base it on a Table. However, you
mention
2002, which lacks this capability.

What are your name definitions and series formulas (in 2002). There
may
be
alternative ways to define the names.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"goofy11" wrote in message
...
I've recently realized that Excel 2007 has a bug that is preventing
me
from
using dynamic Named ranges to create dynamic charts. Several people
have
noted on this newsgroup that they've experienced similar problems
with
Excel
2007 charting using named ranges. In my case i get the error
message
"A
formula in this worksheet contains one or more invalid references."
When
I
open this same workbook at home (using Excel 2002), everything works
fine.
SP1 addressed something similar to the issue I'm having, but in my
case
SP1
did not fix the problem. As a result I'm wondering if there are any
alternatives to using Named ranges? This is going to be a template
accessed
by multiple users from the web. As a result, it needs to be
compatable
with
older versions of Excel and not use VBA.

Does anyone have any workarounds, or am I out of luck? The Series
ranges
could expand or shrink.

Jeff