2-step Dynamic Ranges and Charts

[Nick Loughton]

Hi.

This is something I know I was able to do about 20 years ago! It may be that I've not quite got the intermediate steps right, or current versions of Excel approach it in a slightly different way, but any suggestions would be gratefully received.

I have a large, single page, set of data. It has dates down the rows, about 40 different fields across the columns (I'm only interested in about 30). But it is subdivided, down the rows, into regional information. In very simple terms it looks a bit like

Though in reality the dates are monthly and run for many years, and the data is also presented at a sub-regional level. I'm interested in South East, South West etc, but not named towns.

I want to be able to look at a subset of the whole time series. Eg just last [5] years, not all 40 years. This is done with either an input for number of months, or select a date and then match the date to the relevant row. So I have a load of VBA generated names of the form: Dates_S, Index1_S, Index2_S, Dates_W, Index1_W etc etc. They are all dynamic and use OFFSET() to size, as well as define the start point.

Even the Date ranges will be slightly different (same number of cells, just starting from a different point), as they all match the rows in the main data set that would be used for the important fields (Index1 etc)

All of this works fine. I have a short routine that writes all the named ranges onto a separate sheet, with hyperlinks and the text for the RefersTo address, and I can see that for my 13 different regions (using 13 different suffixes) each of the 30 fields (Index!, Index2 etc in simple example above) I have a dynamic range that does indeed change depending on what length of history I want to show, and does pick up the correct part of the larger data set. Note, all these names have Scope as WorkBook (I'm not sure if this makes a difference but I have seen a post somewhere else here suggesting it may.)

And this gets to the problem. The final step it to have a single sheet that shows graphs for each of my [30] data items, but will change based on the region selected, via a dropdown. So I have a second set of Named Cells (not ranges), of the form

grDates, grIndex1, grIndex2 etc

The value in each of these will then be, via the drop down, Dates_W; Index1_W etc or Dates_S; Index1_S depending on which region is selected to view.

I want to add an INDIRECT() into a chart, so that the Chart will always contain the same series - grIndex1 & grDates - but that the data shown will vary because grIndex will link through, by INDIRECT() to Index_S, say.

As I say, I used to use this a lot 20yrs ago, but now cannot seem to get it to work.

I cannot add a series to a chart using INDIRECT(grIndex1) as the data points, or by extension INDIRECT(grDates) as the x-axis.

I've looked at intermediate steps to see where it fails. eg
COUNTA(Index1_S) gives the right answer (the number of rows, based on the time period chosen)
BUT
COUNTA(INDIRECT(grIndex1)), where the value of grIndex1=Index1_S returns a value of 1, and stepping through the evaluation shows that it looks like it is trying to calculate
COUNTA(INDIRECT("Index1_S")) - so it is correctly picking up that grIndex1 has the value Index1_S, but maybe the " " suggest it is not then treating it as the named range it is.

It may be that there is a better ("modern"!) way of doing this with data tables, power pivots etc. I need about 30 graphs for each region. It's been a long while since I've got stuck into this kind of thing! I'm enjoying it but getting frustrated!

(Ideally, as a further step, I will create these graphs from VBA, just looping over a list of names: grIndex1, grIndex2 etc)

Any suggestions as to how to get past this final hurdle? Many thanks