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) withoffsetformulas 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 withOffsetformulas
When I have 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
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) withoffsetformulas 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 withOffsetformulas
When I have 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