Dynamic name range missing from graphs after making copy of a worksheet

Hi Everyone,

I would be glad if you can help me regarding this please.

I have an Excel sheet in which I have graphs which use dynamic name ranges (these name ranges refer to the worksheet and not workbook). When I copy this Excel sheet, the dynamic name ranges copy to another sheet, however, they vanish from the graphs in the new sheet.

For example, in the original sheet (called "Retirement"), one of the graphs uses the series value as =SERIES(Retirement!$CJ$3,Retirement!$AY$374:$AY$1014,Retirement!Asset1_AssetAllocation,1)

Once I copy the worksheet to another sheet called "US House", the series value in the graph changes to =SERIES('US House'!$CJ$3,'US House'!$AY$374:$AY$1014,'US House'!$BA$414:$BA$1015,1)

How can I retain the dynamic name range in the graphs in the sheet "US House"?

Thanks in advance!

Suggest not copying the entire worksheet because it probably contains the data that the graph was based on, just the graph portion and paste it into a new worksheet. Leave the source data where it is.

Let us know what happened.

Pete

Thanks a lot Pete for you reply.

You are right, if I only copy the graph to the other sheet, then the dynamic name range remains in the copied graph.

However, this does not solve my problem. Because I want the graph which is copied to the new sheet to reference to data in the new sheet and not the old sheet.

Just to be clear,

1) I have data and graph in Sheet X. I have created dynamic name ranges which refers to data in Sheet X and which the graph uses.

2) I make a copy of Sheet X - lets call it Sheet Y. When I do this, the dynamic name ranges in Sheet X also gets copied to Sheet Y. For instance, I have a dynamic name range in Sheet X which goes:

Data_RetirementCorpus=INDEX(Sheet X!$CZ:$CZ,MATCH(Sheet X!$DE$10,Sheet X!$CZ:$CZ,0)):INDEX(Sheet X!$CZ:$CZ,MATCH(Sheet X!$DE$14,Sheet X!$CZ:$CZ,0))

When I make a copy of Sheet X and name it Sheet Y, in Sheet Y, I again have a dynamic name range which goes like this,

Data_RetirementCorpus=INDEX(Sheet Y!$CZ:$CZ,MATCH(Sheet Y!$DE$10,Sheet Y!$CZ:$CZ,0)):INDEX(Sheet Y!$CZ:$CZ,MATCH(Sheet Y!$DE$14,Sheet Y!$CZ:$CZ,0))

3) However, from the graph in Sheet Y, the dynamic name range goes missing.

I hope this clarifies.