Dynamically Updating Chart - Multiple Series based on Advanced Filter

[alexbell2]

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

I attached the file with the bloomberg links hardcoded.

Any help on this would be greatly appreciated!