• 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...

  • 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

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.
 

Attachments

Hui

Excel Ninja
Staff member
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
 

Attachments

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?
 
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?
 
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.
 

Greenbriars

Member
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:
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.
 

Attachments

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.
 

Hui

Excel Ninja
Staff member
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
 

Attachments

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.
 
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?
 

Hui

Excel Ninja
Staff member
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 ?
 

Attachments

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?
 

Attachments

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.
 

Hui

Excel Ninja
Staff member
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
 
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.
 

Attachments

p45cal

Well-Known Member
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
 

Attachments

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.
 
Top