Dynamic chart based on named range gets "locked" ? corrupted??

[Swamp Thing]

I have a workbook that has evolved over time and has several static charts (based on fixed data ranges).

I am trying to make these charts dynamic and have run into this strange problem. When I go into "select data" and enter the dynamic range names, it adjusts itself correctly according to the named range.

However, if I change an input parameter that affects the length of the range, the chart gets corrupted with the following symptoms:

1. If I click on a series, the series formula does not appear in the formula bar.

2. If I now click in the formula bar, no cursor appears and it refuses to take any text input

3. If I go into select data and look at the formula, I find that the workbook part of the series formula has become [0]. For example,
'my_workbook.xlsm'!my_range has become [0]!my_range and this happens for the x-values (categories) and the y-values

4. If I create a new sheet and set up a new chart, it works perfectly and continues to work fine.

5. If i copy this good chart into the old sheet "bad" the same problem happens with the copy.

Has anyone experienced this, and is there a solution?

Thank you,

s.t.

 

[Swamp Thing]

I have run into the same problem in another project, so I'm bumping this up with this additional information: the problem occurs when the dynamic range refers to a different sheet from the one the chart is on.

I have worked around it by creating a copy of the original data in the same sheet as the chart (e.g. = Other_Sheet!a1) then defining my dynamic ranges off the copy.

Has anyone noticed this and found a solution?

Thanks,
S. T.