Named range as chart data reference (error)
 

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

Named range as chart data reference (error)- More info
 

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

Named range as chart data reference (error)- More info
 

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

Named range as chart data reference (error)- More info
 

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

Named range as chart data reference (error)- More info
 

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

both still autotransform to
workbookname.xls!named_range

Probably because your name is a workbook-level name. You can access a
workbook level name in some cases (i.e,. chart series formulas and source
data input boxes) even by prefixing with the worksheet name.

The other day I rebuilt a client's workbook, which was misbehaving (names,
bloat, array formulas, and much more, but fortunately not a lot of off-sheet
links to get wrong the first three times), and would crash every five
minutes. Whatever was wrong hasn't been back.

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


"Keith R" wrote in message
...
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

Named range as chart data reference (error)- More info
 

Jon- thank you for your reply.

Since my workbook is also 'misbehaving' (bloat, plus this graph source data
reference issue), do you have any suggestions for the easiest way to rebuild
a workbook? I'm not sure if I should just copy/paste each sheet into a fresh
workbook (fixing formulas along the way so they don't point to the old
workbook), or if there is anything else I need to do.

Thank you for any advice,
Keith

"Jon Peltier" wrote in message
...
'workbookname.xls'!named_range
and
'worksheetname'!named_range

both still autotransform to
workbookname.xls!named_range

Probably because your name is a workbook-level name. You can access a
workbook level name in some cases (i.e,. chart series formulas and source
data input boxes) even by prefixing with the worksheet name.

The other day I rebuilt a client's workbook, which was misbehaving (names,
bloat, array formulas, and much more, but fortunately not a lot of
off-sheet links to get wrong the first three times), and would crash every
five minutes. Whatever was wrong hasn't been back.

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


"Keith R" wrote in message
...
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

Named range as chart data reference (error)- More info
 

I always start with a new workbook with as many blank sheets as the
original. I name the sheets to match, copy each original sheet, then paste
special formulas and paste special formats into the new sheet. Sometimes I
have to paste special other things as well, for example, comments. Along the
way I make sure that any names have been defined in the new workbook. Since
it's often the charts that go sour, I rebuild all the charts, which can be
the most tedious part.

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


"Keith R" wrote in message
...
Jon- thank you for your reply.

Since my workbook is also 'misbehaving' (bloat, plus this graph source
data reference issue), do you have any suggestions for the easiest way to
rebuild a workbook? I'm not sure if I should just copy/paste each sheet
into a fresh workbook (fixing formulas along the way so they don't point
to the old workbook), or if there is anything else I need to do.

Thank you for any advice,
Keith

"Jon Peltier" wrote in message
...
'workbookname.xls'!named_range
and
'worksheetname'!named_range

both still autotransform to
workbookname.xls!named_range

Probably because your name is a workbook-level name. You can access a
workbook level name in some cases (i.e,. chart series formulas and source
data input boxes) even by prefixing with the worksheet name.

The other day I rebuilt a client's workbook, which was misbehaving
(names, bloat, array formulas, and much more, but fortunately not a lot
of off-sheet links to get wrong the first three times), and would crash
every five minutes. Whatever was wrong hasn't been back.

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


"Keith R" wrote in message
...
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

Named range as chart data reference (error)- More info
 

Great, thank you for the input- I can definitely mimic that process. Now I'm
noodling whether I should (or even could) just replicate those same steps in
VBA, like a poor man's version of Rob Bovey's code cleaner, only for the
non-VBA side of the workbook. I don't know that I'm advanced enough to
replicate graphs (just for lack of experience with the graph object model)
but the rest of it seems fairly straightforward. Maybe next year, when I
have more time ;-)

Thanks again Jon-

Keith

"Jon Peltier" wrote in message
...
I always start with a new workbook with as many blank sheets as the
original. I name the sheets to match, copy each original sheet, then paste
special formulas and paste special formats into the new sheet. Sometimes I
have to paste special other things as well, for example, comments. Along
the way I make sure that any names have been defined in the new workbook.
Since it's often the charts that go sour, I rebuild all the charts, which
can be the most tedious part.

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


"Keith R" wrote in message
...
Jon- thank you for your reply.

Since my workbook is also 'misbehaving' (bloat, plus this graph source
data reference issue), do you have any suggestions for the easiest way to
rebuild a workbook? I'm not sure if I should just copy/paste each sheet
into a fresh workbook (fixing formulas along the way so they don't point
to the old workbook), or if there is anything else I need to do.

Thank you for any advice,
Keith

"Jon Peltier" wrote in message
...
'workbookname.xls'!named_range
and
'worksheetname'!named_range

both still autotransform to
workbookname.xls!named_range

Probably because your name is a workbook-level name. You can access a
workbook level name in some cases (i.e,. chart series formulas and
source data input boxes) even by prefixing with the worksheet name.

The other day I rebuilt a client's workbook, which was misbehaving
(names, bloat, array formulas, and much more, but fortunately not a lot
of off-sheet links to get wrong the first three times), and would crash
every five minutes. Whatever was wrong hasn't been back.

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


"Keith R" wrote in message
...
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

Named range as chart data reference (error)- More info
 

That's pretty ambitious. The VBA modules are basically simple text files,
and there is already export/import functionality for it.

For a few of my projects I've written "Archive" features, which basically
means making a copy of a workbook, with values instead of links and pictures
instead of charts. It is a tedious operation, and every so often one of the
users discovers a feature of a workbook which crashes the Archive code and
must be dealt with.

Good luck.

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



"Keith R" wrote in message
...
Great, thank you for the input- I can definitely mimic that process. Now
I'm noodling whether I should (or even could) just replicate those same
steps in VBA, like a poor man's version of Rob Bovey's code cleaner, only
for the non-VBA side of the workbook. I don't know that I'm advanced
enough to replicate graphs (just for lack of experience with the graph
object model) but the rest of it seems fairly straightforward. Maybe next
year, when I have more time ;-)

Thanks again Jon-

Keith

"Jon Peltier" wrote in message
...
I always start with a new workbook with as many blank sheets as the
original. I name the sheets to match, copy each original sheet, then paste
special formulas and paste special formats into the new sheet. Sometimes I
have to paste special other things as well, for example, comments. Along
the way I make sure that any names have been defined in the new workbook.
Since it's often the charts that go sour, I rebuild all the charts, which
can be the most tedious part.

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


"Keith R" wrote in message
...
Jon- thank you for your reply.

Since my workbook is also 'misbehaving' (bloat, plus this graph source
data reference issue), do you have any suggestions for the easiest way
to rebuild a workbook? I'm not sure if I should just copy/paste each
sheet into a fresh workbook (fixing formulas along the way so they don't
point to the old workbook), or if there is anything else I need to do.

Thank you for any advice,
Keith

"Jon Peltier" wrote in message
...
'workbookname.xls'!named_range
and
'worksheetname'!named_range

both still autotransform to
workbookname.xls!named_range

Probably because your name is a workbook-level name. You can access a
workbook level name in some cases (i.e,. chart series formulas and
source data input boxes) even by prefixing with the worksheet name.

The other day I rebuilt a client's workbook, which was misbehaving
(names, bloat, array formulas, and much more, but fortunately not a lot
of off-sheet links to get wrong the first three times), and would crash
every five minutes. Whatever was wrong hasn't been back.

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


"Keith R" wrote in
message ...
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