Dynamic Chart and Table, cannot copy into new workbook

[ridradny]

Hi,

Brand new at posting to the forum but have found advice on here regularly.

Background:
I was assigned at my workplace to create an S curve generator within excel that spits out the cumulative and periodic values and percentages over a specified time period and with a specified amount of Units (this can be dollars, kwh, anything basically over time) and you choose between 3 different algebraic formulas depending on the size and scope of your project.

I have made this successfully dynamic so that the graph and the table are completely dynamic based on all my inputs, to make the graph dynamic I have named ranges mainly for simplification and have not used any macros or VBA code etc.

The problem I have run into is some colleagues would like to copy the sheet that has the chart and table on it (this has all of the inputs etc in it also no links to other sheets). Everything seems to work fine once it is copied except the named ranges are not copies. The named ranges are added to the new workbook but when I go to select data on the graph the named ranges have been replaced with the absolute cell references. I can't change it manually either. I have been working on this problem for a few hours and i'm completely stumped at this point. Any advice appreciated

Thanks.

 

[Andrew Poulsom]

What do your names refer to? Are they worksheet level names or workbook level names? How are you copying the worksheet? It may also help to know the Excel version.

 

[ridradny]

Excel 2016,
Names refer to lookup functions, eg. the name "Tbl" refers to =INDEX(Miskawi!$C:$C,ROW(Miskawi!$C$29)+1):INDEX(Miskawi!$F:$F, ROW(Miskawi!$C$29)+Miskawi!$D$12)
tblper refers to =INDEX(Tbl, 0, 1)

The names do not apply to entire workbook just the individual sheets. When I copy or colleagues have copied the sheet we have tried right click the sheet name tab and copy into new workbook, and we have tried CTRL A then paste special.

hope that clears it up a little

 

[Andrew Poulsom]

What is the Scope of the names in Name Manager - Worksheet (Miskawi) or Workbook? What are the SERIES formulas for the Series on the Chart?

 

[ridradny]

The scope of the names in name manager are worksheet only.
the SERIES formula for the series chart are as follows.

Series 1
Series Name: 'Period Frac'
Series Values: Miskawi!TblPerFrac

Series 2
Series Name: 'Cum Frac'
Series X Values: Miskawi!TblPer
Series Y Values: Misakaw!TblCumFrac

Name Manager:
Tbl=INDEX(Miskawi!$C:$C,ROW(Miskawi!$C$29)+1):INDEX(Miskawi!$F:$F, ROW(Miskawi!$C$29)+Miskawi!$D$12)
TblPer=INDEX(Miskawi!Tbl, 0, 1)
TblCumFrac=INDEX(Miskawi!Tbl,0,COLUMNS(Miskawi!$C$29:$D$29))
TblPerFrac=INDEX(Miskawi!Tbl,0,COLUMNS(Miskawi!$C$29:$E$29))


When I copy the sheet the Names that are in the chart series change to absolute cell references and are therefore no longer dynamic.

 

[Andrew Poulsom]

Does it work if you move the worksheet rather than copying it? You will need to close the original workbook without saving to avoid losing the original sheet.

Here's an article on the subject:

Make a Copied Chart Link to New Data - Peltier Tech Blog

 

[ridradny]

I actually tried moving the sheet instead of copying after posting my last reply and same result unfortunately.

Interestingly i seem to be able to replace the series formulas with the names now. However only series 1 is dynamic series 2 is not dynamic?

Its very very strange, i may just have to accept the limitation.

 

[Andrew Poulsom]

Moving worked for me with your setup. Here are the SERIES formulas:

=SERIES(Miskawi!$D$29,Miskawi!TblPer,Miskawi!TblCumFrac,1)

=SERIES(Miskawi!$E$29,Miskawi!TblPer,Miskawi!TblPerFrac,2)

 

[ridradny]

Thanks for the reply. Ill input those new series formulas when i get to work tomorrow.

One series is a bar chart. The other is a smooth line chart that needs x and y

 

[ridradny]

Hi,

I tried you series formula, it's the same as what I have already is it not? but i'm not sure where your x and y values are.
My chart is a 2 way chart with a cumulative line graph and period bar graph all in one. So I need the x and y values for the line graph. below are what the original series formula are
Series 1
Series Name: 'Period Frac'
Series Values: Miskawi!TblPerFrac

Series 2
Series Name: 'Cum Frac'
Series X Values: Miskawi!TblPer
Series Y Values: Misakaw!TblCumFrac

Thanks for your help.