Dynamic charts without Named Ranges?

[goofy11]

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 Peltier]

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

 

[goofy11]

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 FORMULA
=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]

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'!Dates,'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

 

[goofy11]

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]

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

- Jon