Excel Chart Series Do Not Retain Links to Dynamic Named Ranges And Reset Back to Static Ranges

[rkaczano]

Keypoint here is my data set is uses a Filter () function expand and contract the data set. And the graph it feeds loses the links back to the named ranges of that data set.

I have a dynamic data set in Excel on Sheet1 in a workbook in range A1:A10. Each row in rows 1 to 10 use the FIlter() function to populate the data set from another worksheet.

That data set then feeds an Excel chart. Each row in the data set has a Named Ranges. For example in row 1 the named range is called Item1 = cell A2# and for row 2 the named range Item2 = cell A3# etc. All good - data set updates properly using Filter() function.

I then create series in the chart that link back to those dynamically named ranges. When the data table changes the chart is supposed to update dynamically. To do link the chart series properly in Excel I have to precede the dynamic named range for each chart series with the name of the worksheet that the named range resides in (for example series 1 =Sheet1!Item1 and you cannot simply link the series to = Item1). So I have to do this for a all the series in the chart. So as the data set expands and contracts (A1:A10 or A1:D10 or A10:ZZ10 etc) each named row in the data set dynamically expands and each chart series is updated. This is due to the use of the “#” after each cell in the named ranges. All looks good when I manually link each series as described above.

The problem I am having is that the chart series don’t seem, to retain the links to each named dynamic ranges but instead each series reverts back to a static ranges. So for example if I set up my filtered data set to have an active range of A1:K10, and then manually relink all the chart series to the individual named ranges in the rows in the data set, each series properly links and the chart look fine. But when I then change the filtering data and the data set re-populates to an active range of say A1:D10, all the chart series reset back to static range values form the last filtered run (for example Series 1 resets from =Sheet1!Item1 to Sheet1!A1:D1.

Any ideas why this is happening and how do I fix this? I have not had this problem before.

Thanks

 

[awoohaw]

It would be very useful for the folks of the forum to actually see what is happening? Any reason why you can't provide a mini workbook via the xl2bb tool? Or at minimum a table that we can copy and paste values and attempt to replicate your error?

 

[rkaczano]

Just realized what the issue is.

As noted in my description, you cannot create 1 dynamic range for the data set feeding the chart. Excel forces you to create dynamic ranges for each range that feeds the chart. No issue there as I did this.

But when you are doing this your have to also set the axis label for each new series.

When I linked the first series to say =Sheet1!Item1 I also had to link its axis labels to my dynamic column heading - say = Sheet!Item0. And I did that correctly.

Now when you click on “Add Item” to add another series, Excel does not know what you want for axis labels. So when you link that second series to a named range = Sheet!Item2, the axis labels window (right window in dialogue box) simple adds hard-coded numbers to the axes label based on what it infers from your dynamics range for the series data (i.e. 1,2,3,4,5,6,7,8 etc). I actually missed this and was assuming that the dynamic named range of axis labels from series 1 was automatically being provided for series 2. But it does not automatically assume this.

As series 2 has hard coded default axis labels (1,2,3,4,5,6,6, 7, 8 etc), when you change the filtering criteria in the underlying data set and that data set expands/contracts (the column data increases/decreases), the card-coded axis labels for the series you did not update in the chart must force the chart to conform to those hard coded references for all series and this must reset all the links for all the services from the named ranges to ranges that conform to the hard coded column # references. So even though my first series has the right axis label, if my remaining series had default axes labels of (1,2,3,4,5,6,7,8), upon the data set recalculating, all my series reset to a static range with 8 columns. So row 1 when from a dynamic range to A1:G1, row to A2:G2, etc

Glad I caught this.