Dynamically updating Charts with new and different data

[Pete Mccann]

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.

 

[Hui]

Pete

Done


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

 

[Pete Mccann]

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?

 

[Hui]

See how this goes?

 

[Pete Mccann]

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?

 

[Hui]

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?

 

[Pete Mccann]

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.

 

[NARAYANK991]

Hi ,

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

See the attached file.

Narayan

 

[Greenbriars]

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.

 

[Pete Mccann]

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.

 

[NARAYANK991]

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

 

[Pete Mccann]

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]

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

 

[Greenbriars]

Hui

I have tried to load your new file with exactly the same "file corrupted" message

 

[Pete Mccann]

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.

 

[Hui]

I will try it in 365 tomorrow, Midnight here

 

[Pete Mccann]

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]

I opened both versions in Excel 365 with no problems



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 ?

 

[Pete Mccann]

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?

 

[Hui]

Can you please try this
I replaced the Aggregate function that may not be available in some Excel versions

 

[Pete Mccann]

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]

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

 

[Pete Mccann]

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.

 

[p45cal]

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:

 

[Pete Mccann]

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.