Jon- thank you for your reply. Unfortunately, this is in Excel 2003 (sorry I
forgot to mention that in my original post). It is discouraging to think
that the problem is the same (or worse) in 2007. I saved the file under a
new filename, but find that both

'workbookname.xls'!named_range
and
'worksheetname'!named_range

both still autotransform to
workbookname.xls!named_range

which then gives the same symptom, where Excel doesn't consider it a valid
formula.

At this point, I'm inclined to rebuild the workbook (it is bloating as well)
so maybe I'll get lucky and the problem will dissapear when I do so. I'll
open a separate thread in Excel.misc to ask for any tips/tricks on how to
unbloat workbooks without messing up cross-sheet formulas, etc.

Thanks,
Keith

"Jon Peltier" wrote in message
...
Is this Excel 2007? I ran across some issues in this release with names as
chart source data, getting error messages even though the ranges were
valid and the charts updated appropriately. Apparently saving the workbook
with a different name, then quitting and restarting Excel and reopening
the newly named workbook, fixed it.

I've run across other issues in 2007 with names being accepted and charts
not updating until they were saved (not saved as something else) and
reopened. This occurs with only workbook or worksheet level names, I
forget which.

Still some rough edges.

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


"Keith R" wrote in message
...
v11.xls is the workbook name
Graphs is the graphs worksheet name
Main Data is the data worksheet name

When I replace =v11.xls!A_ with ='Main Data'!A_ it accepts it as a valid
range, and the moment I go back into source data to look at it, it has
been automatically updated to =v11.xls!A_ ...and if I click into this
updated name, it still gives me the 'formula contains an error' message

This is very confusing to me :-(

Thanks for any advice,
Keith

"Keith R" wrote in message
...
Based on Steve Bullen's FunChart1 example (in that particular example,
auto-expanding named ranges) I've set up some graphs that used named
ranges as their data source. I copied the worksheet of graphs into a new
workbook and now have global named ranges that match the graphs'
original named ranges (using Jon Peltier's suggestion to use Jan
Pieterse et al's Name Manager v4.0, in which I removed all the sheet
named ranges which were duplicates, leaving me with just one set of
global named ranges)

The graphs, which still have their original named range sources, update
when the raw data updates- but I am unable to edit the named range for
any charts. If I click into the source data entry box (series/values)
and try to click out, I get an error message (formula contains an
error). The references are simple (=v11.xls!A_, =v11.xls!B_, etc.) and
the named ranges point to valid ranges of data (and the graphs update
when the raw data is updated).

A_ =INDIRECT("'MAIN DATA'!$BA$" & Graphs!$A$4 & ":$BA$" & Graphs!$B$4)
B_ =OFFSET(A_,0,1)
C_ =OFFSET(A_,0,2)
etc.
(all show the target data range correctly when I click in the range box
of the named range dialogue)

Based on the original problem with the named ranges, is there anything
that might be residually causing problems with the graphs? Any ideas
what else I should check?

Thank you very much,
Keith