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.

 

[ridradny]

sorry that may not have made sense,
basically no, still no luck and I can't seem to figure out your series formula (I have a bar graph and a line graph so only need x and y for cumfrac values)

 

[ridradny]

UPDATE: I saved the workbook as a different name, then tried moving and the named references have remained intact in the series formula. However the chart is not dynamic, it does not move. when you click the series formula it is selecting the correct amount of data from the table but the graph remains static it wont budge.

 

[Andrew Poulsom]

Do you mean it doesn't change if you change D12?

 

[ridradny]

D12?
No i mean the chart is not dynamic, the chart is meant to move (expand and contract) when I change alpha, beta and volume variables (input data). The chart does this on my original but does not do this when it is moved or copied. It's hard to define/explain without a visual.

I have spent a fair amount of time on it today and came upon a group of engineers having the same problem online, it apparently boils down to a bug in excel not allowing dynamic charts to update named ranges when they are moved or copied. I inserted a macro forcing the axis labels to flip back and forth after they are copied and this seems to have fixed my issue. simply pressing CTRL X then CTRL V also solves the problem quite simply.

Thanks for looking into this for me I appreciate your time.

 

[Andrew Poulsom]

It's D12 that determines the number of rows to plot:

=INDEX(Miskawi!$C:$C,ROW(Miskawi!$C$29)+1):INDEX(Miskawi!$F:$F, ROW(Miskawi!$C$29)+Miskawi!$D$12)

That's the only dynamic part as I see it.

 

[ridradny]

Im away from my desk now. I may have made an error in explaining the named ranges. You're correct there, my problem is it (the chart) won't update when i move the sheet.

For now i seem to have a crude workaround.