Copied from the Word document:

Since this is a rather long explanation, I decided to put it in a word document rather than try to post it.

This dashboard is accomplished using named dynamic ranges defined by the offset command and using these names in the charts.

A more generic explanation is given in the following two wikis:
http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges
http://www.utteraccess.com/wiki/Dynamic_Charting

I assumed that each data set has an identifying number in column A and that the number matches up with the first row of the data set. I also assumed that there is exactly one blank row between data sets. I had to “cheat” and added an “End” data set in Cell A199, so I could find the end of the last data set.

There is an old Beatles’ song: I Get by With the Help of my Friends. In this case, I get by with the help of an entire helper sheet.

Columns A:D are used to find the boundaries of the data ranges. I listed the sets 1-12 (plus End) in column A and use this range for data validation in the drop-down list in Cell C1 on the BT sheet.

Column B uses MATCH to find the start of each data set. Column C assumes that the end of each data set is exactly two rows above the start of the previous set. The difference (Column D) is the number of rows in the data set.

When you make a section in Cell C1 on the data sheet, it is reflected in cell G1 on the helper sheet. This value is used by VLOOKUP (against columns A:D) to look up the start row (offset – Cell G2) and number or rows in the data set.

The baseline row is row 4 on sheet BT, so this amount is subtracted from the sheet position of the offset.

I decided that rather than shift the range on the GT sheet, I would mirror it on the helper sheet and use that range to plot the chart. Columns J:N do this. This table is set up to plot a maximum of 25 rows in a data set, but you can extend the table to plot more.

I will use data set 10 as an example.

Cell J2 is the number of rows to offset from the base cell. Column K is the “X-axis” and columns L:N are the three series.

Cell J2 is the offset for the beginning of the range. In the example, the data we want starts 154 rows below the base row (Cell B4 on the BT sheet). Each row below that corresponds to one row more on the BT sheet. This information is used to look up the data for columns B (Verfahren), I, J and K which are 2016, 2017 and 2016+2017.

We now have more data than we need to plot. It will be “masked” with named dynamic ranges.
Plot_2016 =OFFSET(Plot_Verfahren,0,1)
Plot_2016_2017 =OFFSET(Plot_Verfahren,0,3)
Plot_2017 =OFFSET(Plot_Verfahren,0,2)
Plot_Verfahren​=OFFSET('Helper Sheet'!$K$2,0,0,'Helper Sheet'!$G$3,1)

Plot_Verfahren is the most important one. Once you have a basic range established, you can define other ranges from it.

The offset formula has 5 parameters:
Starting Cell (K2)
Number of rows to go down (0)
Number of columns to go right (0)
Number rows to return (whatever is in Cell G3)
Number of columns to return (1)

This pares the data down to the first 11 rows in the “table.”

These names are then used to build the chart (see references).