need VBA help to auto update series values in DYNAMIC CHARTS

[Steffen]

Hi All,

It’s my first post in this forum so hopefully I’m following all the rules correctly . Anyways, I hope someone can help me with this this “dynamic charts” question. Here is the situation:

• I am trying to create a bunch of combo charts, and part of the combo is a line chart. For the series values I created a lot of different Names through the Name Manager since I want the data to be dynamic. As a reference, I’m trying to do something similar to here: https://www.youtube.com/watch?v=7le-m8YRP6M

• I already created the chart and now want to change the underlying data for the series values. Without VBA, I'd do the following: I click on the chart, say “Select Data”, click on “Edit Series”, and then type the dynamic range name manually. As an example, the field will now say the following (with AA2_... being the name of a dynamic range I had previously created and I18n_ being the name of my workbook):

=I18N_F13_F16_SG28.xlsm!AA2_LoginConv_20_PT_PT

• The formula for that dynamic range called AA2_... is the following:

=OFFSET(SUMMARY_CHARTS_2!$BA$76, SUMMARY_CHARTS_2!$M$80,0, SUMMARY_CHARTS_2!$M$82,1)

• Everything works beautifully as long as I select the series value manually. The dynamic range changes once I change values in cells M80 and M82 and the chart updates immediately. However, given that I have a few hundred lines in a large number of charts, I want to automate things through vba and automatically assign names of dynamic ranges to the different series values in my chart. My code is the following:

Sub ChangeReference_2()
'
    ActiveSheet.ChartObjects("Chart 10").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.FullSeriesCollection(3).Name = "=SUMMARY_CHARTS_2!R76C53"
    ActiveChart.FullSeriesCollection(3).Values = _
        Worksheets("SUMMARY_CHARTS_2").Range("AA2_LoginConv_20_PT_PT")
'
End Sub

• The code is running through without any problems and, at first glance, everything looks great. HOWEVER, once I look a little closer, the series values do NOT actually pull the data that’s behind the NAME. Instead, it writes down the CURRENT values behind the name and doesn’t update the values once the underlying values behind the name change. So, I expect series values to be:

=I18N_F13_F16_SG28.xlsm!AA2_LoginConv_20_PT_PT

I see, however, the following:

=SUMMARY_CHARTS_2!$BA$165:$BA$177

• Meaning Excel detects that the CURRENT range behind AA2_LoginConv_... is BA165:BA177 in the SUMMARY_CHARTS_2 tab, and then it locks that range in forever. The next time I update my AA2_... range, the chart doesn’t update because it still uses the BA165:BA177 range.

Does anybody have an idea how to solve this?

Thanks a lot in advance!

Steffen

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

Why not use a Table, also known as a ListObject?

Enter your data as usual.
Select a cell within the data and hit Ctrl + T. Answer OK if the suggested range is OK.
Select the data and insert a chart.

From now on, when you add a new row to the Table it will automatically appear on the chart.

 

[Steffen]

Hi and welcome to the MrExcel Message Board.

Why not use a Table, also known as a ListObject?

Enter your data as usual.
Select a cell within the data and hit Ctrl + T. Answer OK if the suggested range is OK.
Select the data and insert a chart.

From now on, when you add a new row to the Table it will automatically appear on the chart.

Hi there, thanks a lot for your quick reply!

Will this also work if I'm not actually adding or removing rows from my data but just change the rows that I want to include in my chart from that table? To be more specific: Let's say I have 500 rows of data, each row containing the sales data for one week. If I want to see all the data plotted in my graph, I'd want rows 1-500 to be included. However, I want to use this tool to quickly change the time period I want to include in my chart. Meaning I only want to look at rows 47-96, for example, or I only want to look at rows 340-466. I am not, however, changing the actual data in rows 1-500.

What do you think?

Steffen

 

[RickXL]

It is quite easy to try.

Create a new workbook.
Label columns A and B "X" and "Y".
Enter 0 in A2 and do a Ctrl + drag to increment the number for as many rows as you like.
Select just one cell in column A and hit Ctrl +T. It should select the whole range and when you say OK the rows should be alternately coloured with dropdowns at the top.
Enter =RAND() in cell B2 and it should automatically copy down.
Select all the data and insert a Scatter Chart.

Now play with the data, make selections with the dropdowns and add rows etc ...

regards,

 

[Steffen]

ok, thanks, I'll give it a try and let you know if I run into any issues.