Dynamic charts

[Williamm]

Hello))

I want to create a dynamic chart to analyze the inflation rate of some countries for some years. On my range, I have the dates on the X-axis(B2:G2) and the countries on the Y-axis(A2:A5). I am making a dynamic chart that will auto-update when a new data is added to the range (I am using the combination of OFFSET and COUNTA). Everywhere I looked, there are guides on how to create a dynamic chart which will track the changes if a new column is added (a new date in H2)(I successfully did this). Now I want the dynamic chart to auto-update if a new row is added (a new country in A6).

Using the table feature to build a dynamic chart is not doing the job either. As far as I understand, the reason is, as my range takes values from another source (I am using index and match for this purpose), the table fails to auto-expand (unless I enter the data manually).

Are there any ideas how should I proceed?



THREAD MOVED BY MODERATOR

 

[vletm]

The 1st step is to
Upload a Sample File to get a quicker response

 

[Williamm]

I want my dynamic chart to look like this: it auto-updates, when I add a new country, not a new date.

 

[vletm]

One possible ... with Country-filtering.
Despite Your hidden 'combinations' ... index&match
and
Did You really mean that Your chart NOT update if add a new date...
then it would need some changes.

 

[Williamm]

One possible ... with Country-filtering.
Despite Your hidden 'combinations' ... index&match
and
Did You really mean that Your chart NOT update if add a new date...
then it would need some changes.

Yes. It updates only when I add the new data manually, if there is an formula involved, it does not work.

 

[vletm]

1) You didn't even check previous possible, or counter didn't update... Yes
2) Why need to use those A-J -columns? Why not use 'raw'-data and
still with FILTERING?
3) 'Your updating' ... maybe not only an formula won't solve Your idea.
4) If interesting, then try to check this newer sample ...

 

[Williamm]

1) You didn't even check previous possible, or counter didn't update... Yes
2) Why need to use those A-J -columns? Why not use 'raw'-data and
still with FILTERING?
3) 'Your updating' ... maybe not only an formula won't solve Your idea.
4) If interesting, then try to check this newer sample ...

Vletm,

Thank you very much for your time. This is nearly what I want. Just one more thing. I will use a formula for column P (for countries) linked to another sheet (hence the range can expand or shrink). Your technique works just fine. But when I delete Bulgaria and the data for Bulgaria, the legend sign for Bulgaria on the right side of the chart still remains (the small line). How can I get it disappear. The rest is just fine, thanks.

 

[vletm]

Use FILTER to see countries that You would like to see.

 

[Williamm]

THank you Vletm