Need Help to Automate the excel with dynamic charts, drop down list and table

[Zuhaib_Raja]

Hi All,

 I am trying to create a dynamic chart with drop down list so I could select data according to my own preference date ranges. I have worked on it and created a chart that’s encoded with my data and it works fine with the dates/months that I select. However, I want to make it more dynamic like in a table so if I add more data such as Mar-20, Apr-20 and so on it will automatically show/pop up into my drop down list. I tried to add March 2020 (Mar-20) after creating a table but it didn’t pop up into my drop down list neither in the chart since the chart only pick ups the values from drop down data values.

I really appreciate if someone could help me in this regard.

Note: The attached file has 2 tabs in the excel worksheet (Data & Chart). Please check both

Thank you in advance

[#2]

My recommendation would be that you back up and reorganize your data into more of a basic database table, and then avail yourself of the Pivot Table and related charts. You'd then be able to simply add new rows of data on each type of insurance, for each month, and have the Pivot Table and any chart automatically incorporate those new months' data.

You've made it more complicated by already laying out the data and analyzing it in your table. You've confused the raw data (INPUT) end of things with the summary analysis (OUTPUT) end of things.

In an Excel Table such as I'm suggesting you'd have column headings like
MONTH, LOANTYPE, AMOUNT
and your data could fit into those three categories.
Pivot Table could automatically show a cross tab summary of amounts and percentages.

[#3]

@mathetes 

 I forgot to mention on my previous message that I can't change the layout, make amendments in the existing datasheet in terms of formating. This datasheet has been working since ages, no idea how long but for sure before hiring me. I have shared the sample data publicly. Would you please recommend me with same logic what I am using? 

 Also, I would happy to see what you are demonstrating. Will it be possible for you to share the example with my data sheet that I have attached earlier?

[#4]

@Zuhaib_Raja 

Here's an example of what reorganized data can do. You can still report in the kind of format you've been using, and the Pivot Table capability makes it a lot easier. I did not take time to clean it up to make it exactly like yours. But it should serve to demonstrate that it's possible. When new rows get added to the table, all you need to do is, under the Data tool bar, click on "Refresh all" and it all gets incorporated into the Pivot Table.