Copied graphs not working

[Keith R]

I had a workbook of data with a number of graphs in one worksheet; each
graph series was populated by a named range (A_, B_, C_, D_, etc.). A_ was
the only direct (actually, indirect) reference, and the other series are all
offsets of A_

I had some new data in a new workbook, so I used the worksheet/copy/move
functionality to make a copy of the worksheet that contains the graphs in my
new workbook. I copied over the named ranges and re-set them in my new
workbook to the new data areas (and verified that they accurately point to
ranges of data- which they do). However, the 'new' graphs do not want to
accept the named ranges in the new workbook.

Workbook name: "v10.xls"

Named Range for the first graph= "A_" which worked fine in the old workbook

In the new workbook, I click on source data for the first graph and click
into the source data fields; just like with the old workbook, the only one
populated is Series1 as =v10.xls!A_ but when I click into the field and try
to close the graph, I get a "formula you typed contains an error" message.

Any ideas what I should troubleshoot?

Thanks,
Keith

 

[Keith R]

This is in XL2003, possible bug?

When I click on a cell in the worksheet that has the graphs (the copy
imported from another workbook) and go into named ranges, A_ shows as:

=INDIRECT("'MAIN DATA'!$BA$" & Graphs!$A$4 & ":$BA$" & Graphs!$B$4)

but when I go into the MAIN DATA worksheet and click on a cell and then go
into named ranges, A_ shows as:

="AllValidData!$AO$" & 'C:\Documents and Settings\RuK\Desktop\GB\[RFT Lot
v6.xls]Graphs'!$A$4 & ":$AO$" 'C:\Documents and
Settings\RuK\Desktop\GB\[RFT v6.xls]Graphs'!

....which is a reference to the datasheet in the old workbook. So, I think my
question needs to be updated- I thought that named ranges were good across
the whole workbook, which means that I'd get an error if a copied/imported
worksheet tried to bring in new named ranges- (1) why is it possible to have
more than one named range with the same name, and is this a bug, (2) is
there any good way to identify which named ranges have duplicates and delete
the 'bad' references? I really need to get these graphs working quickly...

Thanks,
Keith

 

[Jon Peltier]

Names can be defined for the entire workbook or for a worksheet. If the
worksheet is the scope of the name, its name is preceded by the worksheet
name:

Workbook-scope:
TheName

Worksheet-scope:
'My Sheet'!TheName

When you copy a sheet into the same workbook, the workbook-level names
referring to ranges on the copied sheet are converted to sheet-level names.
When you move or copy a worksheet with names referring to ranges on other
worksheets into a different workbook, the workbook names remained
workbook-level in the new workbook, but they kept their reference to the
source in the old workbook. This is because the names pointed to data on
different (non-copied) sheets in the original workbook. Names referring to
ranges on the copied/moved sheet still refer to the ranges on the
copied/moved sheet.

If the new workbook has the correct other sheets and data structure, you can
go to Edit menu > Links, and change the link source to the current workbook
(you have to browse to it).

If you're going to be working with names, the Excel Define Names dialog is
not really adequate, but Jan Karel Pieterse has an outstanding Name Manager
among the free utilities on his web site, http://jkp-ads.com

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

This is in XL2003, possible bug?

When I click on a cell in the worksheet that has the graphs (the copy
imported from another workbook) and go into named ranges, A_ shows as:

=INDIRECT("'MAIN DATA'!$BA$" & Graphs!$A$4 & ":$BA$" & Graphs!$B$4)

but when I go into the MAIN DATA worksheet and click on a cell and then go
into named ranges, A_ shows as:

="AllValidData!$AO$" & 'C:\Documents and Settings\RuK\Desktop\GB\[RFT Lot
v6.xls]Graphs'!$A$4 & ":$AO$" 'C:\Documents and
Settings\RuK\Desktop\GB\[RFT v6.xls]Graphs'!

...which is a reference to the datasheet in the old workbook. So, I think
my question needs to be updated- I thought that named ranges were good
across the whole workbook, which means that I'd get an error if a
copied/imported worksheet tried to bring in new named ranges- (1) why is
it possible to have more than one named range with the same name, and is
this a bug, (2) is there any good way to identify which named ranges have
duplicates and delete the 'bad' references? I really need to get these
graphs working quickly...

Thanks,
Keith

I had a workbook of data with a number of graphs in one worksheet; each
graph series was populated by a named range (A_, B_, C_, D_, etc.). A_ was
the only direct (actually, indirect) reference, and the other series are
all offsets of A_

I had some new data in a new workbook, so I used the worksheet/copy/move
functionality to make a copy of the worksheet that contains the graphs in
my new workbook. I copied over the named ranges and re-set them in my new
workbook to the new data areas (and verified that they accurately point
to ranges of data- which they do). However, the 'new' graphs do not want
to accept the named ranges in the new workbook.

Workbook name: "v10.xls"

Named Range for the first graph= "A_" which worked fine in the old
workbook

In the new workbook, I click on source data for the first graph and click
into the source data fields; just like with the old workbook, the only
one populated is Series1 as =v10.xls!A_ but when I click into the field
and try to close the graph, I get a "formula you typed contains an error"
message.

Any ideas what I should troubleshoot?

Thanks,
Keith

 

[Keith R]

That definitely clears things up (and good to know I'm not just crazy)-
thank you for the thorough explanation, that will help me keep from doing it
again in the future. I've downloaded the names manager you suggested, and
will give that a try as well.

Best,
Keith

Names can be defined for the entire workbook or for a worksheet. If the
worksheet is the scope of the name, its name is preceded by the worksheet
name:

Workbook-scope:
TheName

Worksheet-scope:
'My Sheet'!TheName

When you copy a sheet into the same workbook, the workbook-level names
referring to ranges on the copied sheet are converted to sheet-level
names. When you move or copy a worksheet with names referring to ranges on
other worksheets into a different workbook, the workbook names remained
workbook-level in the new workbook, but they kept their reference to the
source in the old workbook. This is because the names pointed to data on
different (non-copied) sheets in the original workbook. Names referring to
ranges on the copied/moved sheet still refer to the ranges on the
copied/moved sheet.

If the new workbook has the correct other sheets and data structure, you
can go to Edit menu > Links, and change the link source to the current
workbook (you have to browse to it).

If you're going to be working with names, the Excel Define Names dialog is
not really adequate, but Jan Karel Pieterse has an outstanding Name
Manager among the free utilities on his web site, http://jkp-ads.com

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

This is in XL2003, possible bug?

When I click on a cell in the worksheet that has the graphs (the copy
imported from another workbook) and go into named ranges, A_ shows as:

=INDIRECT("'MAIN DATA'!$BA$" & Graphs!$A$4 & ":$BA$" & Graphs!$B$4)

but when I go into the MAIN DATA worksheet and click on a cell and then
go into named ranges, A_ shows as:

="AllValidData!$AO$" & 'C:\Documents and Settings\RuK\Desktop\GB\[RFT Lot
v6.xls]Graphs'!$A$4 & ":$AO$" 'C:\Documents and
Settings\RuK\Desktop\GB\[RFT v6.xls]Graphs'!

...which is a reference to the datasheet in the old workbook. So, I think
my question needs to be updated- I thought that named ranges were good
across the whole workbook, which means that I'd get an error if a
copied/imported worksheet tried to bring in new named ranges- (1) why is
it possible to have more than one named range with the same name, and is
this a bug, (2) is there any good way to identify which named ranges have
duplicates and delete the 'bad' references? I really need to get these
graphs working quickly...

Thanks,
Keith

I had a workbook of data with a number of graphs in one worksheet; each
graph series was populated by a named range (A_, B_, C_, D_, etc.). A_
was the only direct (actually, indirect) reference, and the other series
are all offsets of A_

I had some new data in a new workbook, so I used the worksheet/copy/move
functionality to make a copy of the worksheet that contains the graphs
in my new workbook. I copied over the named ranges and re-set them in my
new workbook to the new data areas (and verified that they accurately
point to ranges of data- which they do). However, the 'new' graphs do
not want to accept the named ranges in the new workbook.

Workbook name: "v10.xls"

Named Range for the first graph= "A_" which worked fine in the old
workbook

In the new workbook, I click on source data for the first graph and
click into the source data fields; just like with the old workbook, the
only one populated is Series1 as =v10.xls!A_ but when I click into the
field and try to close the graph, I get a "formula you typed contains an
error" message.

Any ideas what I should troubleshoot?

Thanks,
Keith