Filtering a Chart - hopefully using a VBA drop-down or pop up rather than filter a table?

[Madraykin]

Hiya

Had some great help on here recently and have managed to build exactly what my boss wanted! Problem is they always want more don't they!!!

I have created an automated and dynamic chart that displays data for Week 1 through to Week 52 for several different products. Spread across the year are poignant dates (up to 8) which I have plotted on the graph as vertical lines using Error Lines.

What I have now been asked to do is to be able to select one of those lines and see the results for 4, 5, 6, 7, 8, 9 and 10 weeks leading up to that week isolated in the chart. As it will be used across the business by people that want it to be as easy as possible - ie they want to click a button rather than run their own filters - I need the process to be as simple as possible.

I'm ok at VBA and advanced in non-VBA Excel but this has me stumped. I'm wondering if there is some VBA code that could be programmed into a user-input pop-up or something that will autofilter the chart?

Anyone have any creative ideas???

Thanking you all in advance!

Mads

 

[thisoldman]

I'm not good with VBA, But I am good at researching. Jon Peltier has a good tutorial on chart event programming: https://peltiertech.com/chart-events-microsoft-excel/. There is a section on using mouse events to trigger the display of a drill down chart.

 

[Madraykin]

Ooooh thanks thisoldman - sorry, didn't get an email to let me know you had replied so have been struggling on this morning trying to find an easier way to do it!

I'm following this tutorial by Debra Dalgleish http://blog.contextures.com/archives/2009/05/03/select-excel-chart-dates-from-a-drop-down-list/

I'm currently testing the long OFFSET string in the Naming the X and Y Values section but am getting a #REF error in the test cell, can anyone see what is going wrong? I've double checked all the named ranges and named cells and everything appears to be in order??

I've basically done everything in this walkthrough but can't get the filters to work.....

I can't insert my Dropbox image for some reason - I know some people are wary of clicking on links....... but it's here https://www.dropbox.com/s/qh3m1nx4y873uk2/FormulaError.JPG?dl=0

If anyone can advise why it won't insert the image let me know!

Thanks in advance

Mads

 

[thisoldman]

The #REF! error is probably caused by Excel not being able to resolve a named formula. You could have deleted a cell, column, or row referred to by the defined name, or perhaps the name was assigned incorrectly. Bring up the Name Manager, Ctrl+F3, and you might spot any errors in the named formulas.