1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamically updating Charts with new and different data

Discussion in 'Discuss Data Visualizations and Charting' started by Pete Mccann, Oct 18, 2017.

  1. Pete Mccann

    Pete Mccann Member

    Messages:
    58
    I have a set of data in the attached file in the "Membership" tab - 5 fields called "Member Name", "Date Member Joined", "Country of Location", "Nationality" and "Membership Type".
    I would like to create a set of "dynamic" charts that reflect additional members joining (thereby increasing / changing the data in "Date Member Joined", "Country of Location" and "Nationality").
    The field for "Membership Type" is static so will not change.
    I have produced examples of the sort of charts that are requested in the "Charts" tab.
    Normally, when a new "Nationality" or a new "Country" are added, I manually add this additional information to the data table in the "Charts" tab and the =COUNTIF calculation does the work. I then manually change the range of the chart and everything is OK.
    For the "When did we join" chart, I manually increase the range if it exceeds the maximum date value in the "Membership" tab and then update the chart.
    Is there a way to automatically update the charts based on the data in the "Membership" tab so that when extra (and different) data is added in the "Membership" tab for the "Nationality", "Country of Location" and "Date Member Joined" fields then the data for the charts is updated without having to manually change the ranges or add extra data.
    I think this calls for a V-Look up style of approach but I have not been able to make a lot of progress on this.

    Attached Files:

  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,828
    Pete

    Done
    upload_2017-10-19_13-0-17.png

    I have made extensive use of Named Formula to
    1. Extract the unique values for the 4 charts
    2. Setup the charts

    see attached

    Please come back with specific questions

    Attached Files:

  3. Pete Mccann

    Pete Mccann Member

    Messages:
    58
    Hi Hui - many thanks for this work. Unfortunately Excel says that the file is corrupted when I try to open it (I also saved it to my PC and the same thing happened.) Excel then crashes. Could you be so kind to re-post your file?
  4. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,828
    See how this goes?

    Attached Files:

  5. Pete Mccann

    Pete Mccann Member

    Messages:
    58
    Sorry Hui - same problem. I can open other Excel files after I tried to open your file. The Membership file comes up in the left pane as a corrupted file so I can see, broadly, what you've done but the formulae show as errors. Can you please zip the file and resend?
  6. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,828
    I have opened it in 2010, 2013 and 2016 without problem

    I have also done a scan and repair and it isn't highlighting any issues ?

    Try this xlsb version or the zip version?

    Attached Files:

  7. Pete Mccann

    Pete Mccann Member

    Messages:
    58
    Thanks Hui - unfortunately Excel is not behaving today and the problem still exists. I will try to fix the problem (I think it must be at my end). I'll get back to you when I find the issue.
  8. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,917
    Hi ,

    A pivot table and chart makes the whole application so simple !

    See the attached file.

    Narayan

    Attached Files:

  9. Greenbriars

    Greenbriars Member

    Messages:
    34
    Hui

    I have tried the xlsx, xlsb and zip files and I too get the "file corrupted" message.

    Excel 2016 32 bit Windows 10 Professional 64bit.

    Hope this helps.
    Last edited: Oct 19, 2017
  10. Pete Mccann

    Pete Mccann Member

    Messages:
    58
    Hi Hui - I have re-installed Office365. My original excel file I sent to you (and other files) all open OK but I'm afraid the xlsx and xlsb from you both cause Excel to crash.

    Greenbriars - thanks for your feedback.

    Narayan - unfortunately I have a problem with your file too. When I open it I can see the chart but I also get two error messages as shown in the attached screen shots.

    Attached Files:

  11. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,917
    Hi ,

    I do not know from where the workbook has got some data connections ; are you using them by any chance ?

    I have deleted all data connections ; see the attached file.

    Narayan

    Attached Files:

  12. Pete Mccann

    Pete Mccann Member

    Messages:
    58
    Thanks Narayan - I was not aware that I was using data connections :))) but the changes to your latest file now work very well. Many thanks. I must do more investigation on Pivot Tables and Pivot charts - there seems to be a huge amount of things that can be done with this idea.
  13. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,828
    Pete

    I had no problems with opening the file even though there was one connection

    I have removed it and saved it again

    See if you can use this?

    ps: I can't verify if it is 365 Compatible as I try to avoid 365 like the plague
    If you have a Normal Excel 2010+ you should have no problems

    There was also a Names Formula "Status" which referred to a file :
    ACE ASN technical AP tracker20100818 is2 - Final 26August2010.xls
    That is also removed

    Attached Files:

  14. Greenbriars

    Greenbriars Member

    Messages:
    34
    Hui

    I have tried to load your new file with exactly the same "file corrupted" message
  15. Pete Mccann

    Pete Mccann Member

    Messages:
    58
    Hi Hui. I really don't understand what is happening here. As soon as I try to open the file, Excel crashes. Unfortunately I don't have any access to any other versions of Excel apart from the one within Office 365.
  16. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,828
    I will try it in 365 tomorrow, Midnight here
  17. Pete Mccann

    Pete Mccann Member

    Messages:
    58
    Cheers Hui. Appreciate all your efforts. If 365 doesn't "work" is there any other earlier Excel format that would still be compatible with 2016?
  18. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,828
    I opened both versions in Excel 365 with no problems

    upload_2017-10-20_11-25-43.png

    I have made a new file which has removed as much as I can and simplified formulas as much as I can

    See how you go with this ?

    Attached Files:

  19. Pete Mccann

    Pete Mccann Member

    Messages:
    58
    Hi Hui, I don't think that you'll be surprised if I tell you that the new "Membership Chandoo2" file also made Excel crash.......
    I have recreated the file from a new blank worksheet so there should be no extraneous links or connections. Could you please try this version?

    Attached Files:

  20. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,828
    Can you please try this
    I replaced the Aggregate function that may not be available in some Excel versions

    Attached Files:

  21. Pete Mccann

    Pete Mccann Member

    Messages:
    58
    This is brilliant Hui. It works. I did notice that there is still one =aggregate function in B3 in the Charts tab though so that might not be the reason for the errors. I will add these formulas and named ranges to the master file to see if I can get the automatic updates working. Many thanks for your help.
  22. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,828
    It sounds like it is the implementation of the Aggregate function not working in Named Formula that is the issue

    It may not Mater as a worksheet function
  23. Pete Mccann

    Pete Mccann Member

    Messages:
    58
    Hi Hui - I hope it is OK to continue this post without creating a new one.
    you did say that I could come back to you if I had any questions.

    I think I understand the concept of what you have done but I am fairly sure I could not have arrived at the same solution. I think that you have essentially set up a set of named ranges and arrays (based on formulas) and then used some of these named ranges to be the x and y axes for the charts. I had not come across this idea before and it is very impressive. I have used your ideas in the attached file and everything is on one sheet (“Membership”). The columns F-P seem to update correctly when new data is added to columns A-D. You had originally identified 4 named ranges for the four columns A-D and an additional 8 ranges for the X and Y axes data for each of the 4 columns in A-D.

    In the attached file, I have tried to set the names for cht_x_Location and cht_y_Location based on your formulae (I did not set up the other ranges yet) but I cannot seem to set up the charts to plot the appropriate named ranges for each of the X & Y coordinates.

    Is there an easy way to set up the chart and then select the X- Axis data and Y-Axis data based on the named ranges cht_x_Location and cht_y_Location.

    Attached Files:

  24. p45cal

    p45cal Well-Known Member

    Messages:
    922
    In the attached, an expansion on Narayan's suggestion (msg#s 11 & 12 above):
    The table on the Membership sheet has been converted to an Excel Table; thiis means:
    1. It can be referred to by its name (It's called Table1)
    2. When you add data to the line below, whether you do it manually or cut and paste from elsewhere, the table automatically expands to accommodate them and retains/extends the data validation dropdowns in the rightmost column.

    On the Charts sheet there are 4 pivot tables each with its own chart. The pivots and charts all use Table1 as their data source.

    Right-click and choose Refresh on any pivot table or chart and they will all update with any added/removed data on the Membership sheet.

    I've left your charts and data in the Charts sheet for comparison, but none of the new charts depends on any data to the right of column O on that sheet, so that data can all be removed safely. I've moved the list for Data Validation to A12:A17 on that sheet too.

    The data on most of the charts has been sorted descending by count, but sort as you wish.
    I've removed all invalid named ranges/Names except I haven't been able to remove two of them in my Excel 2010:
    upload_2017-10-22_19-33-22.png

    Attached Files:

    Thomas Kuriakose and NARAYANK991 like this.
  25. Pete Mccann

    Pete Mccann Member

    Messages:
    58
    You guys are brilliant. I need to spend some time looking at the pivot tables and charts in this file. Many thanks for your help. I'll get back if I have any questions. Really well done.

Share This Page