Problem copying Dynamic graphs with Offset formulas (Excel 2013)

[Derf22]

Problem copying Dynamic graphs with Offset formulas (Excel 2013)
I have a workbook with many sheets with the same graphs plotting different date related information – each sheet has in it its own data
In the first worksheet I have set up dynamic graphs (quarterly rolling) with offset formulas using the data on that work sheet. These graphs are all working dynamically
I now wish to copy this worksheet within the same workbook, rename it and have the graphs work dynamically off the data on that worksheet.
Every time I copy the worksheet and rename it I lose the dynamic function
I would appreciate any assistance regarding the copying of Dynamic graphs with Offset formulas

 

[oldbrewer]

edit, move or copy sheet , check to make copy,rename it by double clicking existing name, tell us which parts are broken

 

[Derf22]

Created and renamed duplicate sheet.

Warning came up re error in formulas / name range – determined it was related to summation formula that does not take adjacent cells into consideration - by design (??)
Message:
We found a problem with one or more formula references in this worksheet
Check that the cell references, range names, and links to other workbooks in your formulas are correct

Original sheet – graphs still react to data changes and are dynamic
Copied / renamed sheet – does not react to changes within sheet itself or if original sheet amended (i.e. is not linked to original sheet data)


Original sheet
· Horizontal axis values – cell range - =File name.xlsx'!chtVal1
· Vertical axis values – cell range - ='Filename.xlsx'!chtCats

‘Sheetnew’
· Horizontal axis values – set to fixed cell range e.g. ='Sheetnew'!$C$6:$C$9
· Vertical axis values – set to fixed cell range = ='Sheetnew!$B$6:$B$9
· Both set to range as at copy stage

Name Manager
Name ranges have been duplicated and are present for both sheets
‘Scope’ on Original sheet = ‘Workbook’ and refers to =OFFSET(chtCats,0,15)
‘Scope’ on Sheetnew = refers to ‘tab name’ and refers to =OFFSET(Sheetnew!chtCats,0,15)
Values on both are depicted thus {…}

I would appreciate your feedback re non dynamic issue
Thanks

 

[oldbrewer]

Sorry,I do not have enough knowledge re this topic to help further, I am sure one of the MVPs will join in in a few hours when USA wakes up.