none
How to update linked data quickly in copied graph RRS feed

  • Question

  • Hello,

    I have a workseet that contains a budget for each month in the year. Now, I need to add graphs that shows that information as a pie chart.

    I have added a new sheet called "Graphs". Inside it, I have added a pie chart whose selected dataset was the corresponding to "January" column, which in my case is column C.

    Now, I have to do the same for other months. So I copied the "January" graph and pasted it into the same Graphs sheet.

    The question is, is it possible to update the column of all references in the new graph? For instance, for "February", column is not C, but D, and so on.

    By doing this manually is a very hard work, so I am wondering if Excel provides a way to do this.

    Regards

    Jaime


    Powered by C++

    Monday, September 25, 2017 1:21 PM

All replies

  • Hi Jaime,

    I suggest you use Macro code to get the result, I will move your thread to Excel for Developer forum:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, September 26, 2017 9:05 AM
  • Hi Jaime Stuardo,

    I think you need a macro to help you do this work.

    Here is a macro example which create twelve charts(one year) from Column C to N.

    Source data is in "Sheet1" and charts will be inserted in "Graphs", you could try to adjust the code for your need.

    Sub AddCharts()
    
    Dim ws As Worksheet
    
    Dim cht As Chart
    
    Set ws = Worksheets("Graphs")
    
    defaultHeight = 216
    
    For i = 3 To 14
    
    Set cht = ws.Shapes.AddChart2(-1, xlPie, 100, 100 + defaultHeight * (i - 3)).Chart
    
    cht.SetSourceData Sheets("Sheet1").Columns(i)
    
    Next i
    
    End Sub

    For using this code, you could use ALT+F11 to open VBE and then use Insert menu to insert a module. Then you could paste the code in the module. Then you could run the macro from Developer Tab->Macros.

    Please refer to below link for more help.

    Create, run, edit, or delete a macro

    Best Regards,

    Terry



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 27, 2017 8:19 AM
  • Thanks.

    Finally, I Have implemented this function:

    Sub ChangeCharts()

    Dim ws As Worksheet

    Dim cht As Chart

    Dim series As series

    Set ws = Worksheets("Gráficos")

    Dim columna As Integer

    columna = 68

    Dim oldFormula As String
    Dim newFormula As String

    For i = 2 To 12

    Set cht = ws.ChartObjects(i).Chart
    Set series = cht.SeriesCollection(1)

    oldFormula = series.formula

    newFormula = Replace(oldFormula, "$C", "$" & Chr(columna))

    MsgBox newFormula

    series.formula = newFormula

    columna = columna + 1

    Next i

    End Sub

    After that, I only needed a few changes to resulting charts.

    Regards

    Jaime


    Powered by C++

    Saturday, September 30, 2017 11:19 PM
  • Hello Jaime,

    It seems that your issue has been resolved, I suggest you mark your solution or helpful post as answer to close this thread. If your issue persists, please let us know.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 3, 2017 8:40 AM
    Moderator