• 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 Chart - Multiple Series based on Advanced Filter

A

alexbell12

New Member
Hi there,


This has had me wracking my brains for some time now and I can't seem to find a solution.


First of all the scenario: The dynamic filter returns the identifier of bonds according to the criteria selected and from there 2 formulas return the yield and the duration of all the bonds respectively.


The chart: I want to plot the yield (Y) against duration (X axis) for the bonds from the same company in the same series. To facilitate this, I made a "left" formula to return the first 4 letters of the company name, e.g. GAZP, LUKO, TMEN. My goal is for whenever the filter is updated, for the graph to automatically update the series based on these 4 letter names.


Where I have got so far: I realise that creating a dynamic graph involves named ranges using "OFFSET" with embedded "COUNTA" yet because this situation involves data that will change based on the advanced filter, this approach alone is not sufficient. I created another column based on a tutorial on this site that removes duplicates in a list and if I do a "COUNTIF" perhaps this can give me number of rows for OFFSET, but i'm still stuck on how to find the first reference for the "OFFSET" function that will dynamically update the series when the filter changes...


Any help on this would be greatly appreciated!
 
Hi alexbell12,


Firstly welcome to the Forums!!


I think you can try converting your range to Table and then filter for what do you want to plot. If that doesn't work for you please upload a sample file.


Read the Three Green Sticky posts that will help you get help faster from this forum.


Regards,

Faseeh
 
Hi Faseeh,


Thanks for your reply and welcome. Unfortunately since this is a work computer I cannot use file sharing websites, however, I posted the same question with a file uploaded on another site. The link is below:


http://www.excelforum.com/excel-charting-and-pivots/875560-dynamically-updating-chart-multiple-series-based-on-advanced-filter.html?p=3004074


I am hopeing for the table to be able to update automatically when the advanced filter criteria is changed so I don't think converting the range to a table will be enough...Hopefully seeing the file will make everything clearer!
 
alexbell12,


I am not member of the above mentioned forum so can't access your file. please upload that file on some file hosting server and paste a "publicly accessible" link here on this thread.


Faseeh
 
Hi Faseeh,


I managed to solve the problem myself using multiple offset formulas. Thx anyway for your willingness to help.


Alex
 
You must log in or register to reply here.
Back
Top