How to Dynamically select a Named range for a Chart

[JF_Sly]

I have a worksheet set up as follows:

Column A is a list of dates
Colums B - Z have list of various data valuses grouped by Various Skills (seperate skill in each Column)

I have Named ranges for each Skill and the Date column Using the Offset function to select the most recent 80 days of dates/data

I want a Chart to select which Skill is displayed (too much data to display all). I can create a Pulldown selection to select the Skill (the the appropriate Skill Named range. However how do I then pass that to the Chart for the Data points? The X axis will always use the named range for the dates (My_dates); I go that part working correctly on the chart. I just can not figure out how to get the correct Named range into the "legend Entries (Series) portion of the Chart. I tried to create a new named range (My_Range) that will pull the result of the dropdown.

i.e. if the dropdown value = SkillA then My_Range would equal ='workbookname.xlsx'!SkillA' I then entered ='workbookname.xlsx'!My_range in the chart series but that does not work.

I can do what I want by creating a seperate worksheet to copy and lookup values bu I am tying to use the existing data worksheet without having to copy the data to another sheet.

Any one know how I can do this (preferably without VBA)

 

[baitmaster]

welcome to the board

you can use VBA to amend the chart itself but this is normally clumsy and over-complicating things. You should instead be able to create a formula-based chart-input line to which the chart is fixed, and simply change the numbers in this line according to your drop-down

To change the numbers in the chart data cells you might consider Index, Match, Choose, Vlookup etc. Looking at your description I suspect CHOOSE combined with MATCH is the way forward

For example, I had 3 ranges named range1, range2, range3, in rows 1:3 and a drop-down selector in A4. The formula =CHOOSE(MATCH($A$4,$A$1:$A$3,0),range1,range2,range3) worked for me. Entering this formula in all cells at the same time as an array formula (Shift+Ctrl+Enter instead of Enter, into all cells simultaneously, resulting in {} being applied automatically around your formula) makes the formula more robust if your input ranges aren't aligned

 

[JF_Sly]

But my question is how do I then pass that named range to the chart?

 

[JF_Sly]

I'm not wanting to use VBA. I want to do via formulas if I can.
Here is a Link to my test file. I have put the data on one tab, the chart on another and my question with screenshot on the 3rd.

 

[baitmaster]

updated version at https://www.dropbox.com/s/kc2a6gwf6ceg2xi/Test1.1.xlsx?dl=0

You can only perform the specific task requested via VBA. Other than recalculations, a macro is required in order to make any changes to your file, such as amend the range used by a chart. My formula-based approach gets around this, but doesn't handle the second part of your request, i.e. to limit the number of days visible to say 30. You could manually hide a section of cells to achieve that - I've done so and used conditional formats to highlight the values you want, but ultimately your specific requirement is best met using VBA

 

[baitmaster]

Update, I've just looked again at the way that you've passed your data to the chart. I've been slightly distracted by trying to amend the named range and missed what you're doing, and I think there is a formula-based approach that merges your technique with mine and achieves what you want. Where you're using ,1) within your offset, you could probably replace this with a MATCH formula like I've used. I'll go play with it now and check

 

[baitmaster]

created a new named range "skillUsed" and made this dynamic in 2 dimensions, not just 1 like you're doing with your named ranges

https://www.dropbox.com/s/5mhw6lkfmd73kla/Test1.2.xlsx?dl=0

 

[JF_Sly]

I'll have to wait until I get back home to take a look at it, can not DL from work.

 

[JF_Sly]

Was able to take a look at the file and it worked like I was wanting. Thanks, I appreciate the help.