I have designed a couple of graphs using named ranges to give dynamics to the number of series in a chart. This works fine as long as I don't want to copy and/or move the sheet to a new work book. When I do this, Excel chooses to remove all references to the named ranges in the SERIES forumla and replace them with fixed references (despite having scoped all named ranges to WS scope).
From googling I found the tip to save the WB as a template and use Insert sheet to add the graphs. This does not quite work as expected though. The SERIES formula points to the right named ranges, but when I update data the graph is not updated. If I save the new workbook and reopen it, it works as expected. Other quirks include not being able to Insert a second template without saving and reopening (the named ranges points to wrong sheet if I do not do this).
So my question is two-fold; has anyone seen this problem before and solved it? Or, has anyone been able to successfully copy/move a sheet and keep the named ranges without involving a "relink" macro?
From googling I found the tip to save the WB as a template and use Insert sheet to add the graphs. This does not quite work as expected though. The SERIES formula points to the right named ranges, but when I update data the graph is not updated. If I save the new workbook and reopen it, it works as expected. Other quirks include not being able to Insert a second template without saving and reopening (the named ranges points to wrong sheet if I do not do this).
So my question is two-fold; has anyone seen this problem before and solved it? Or, has anyone been able to successfully copy/move a sheet and keep the named ranges without involving a "relink" macro?
