Get data from a fixed length of range, but the range is dynamic

Hi all,

I would like to ask something about getting data from a range with a fixed length, for example 10, but the range will have to be dynamic (I know I sound crazy now, but please bear with me for another two minutes with the example below).

range.PNG

In this tab, I have data from a table starting from column D of 2004, to column P, 2016. The table is fixed to this column in this worksheet, so it can only expand by going to column Q, 2017, and then column R, 2018, etc.

I am getting data from one tab, and then plotting a chart in another tab. However, each time I would only need a series of 10 years, and in this case, from 2007 to 2016.

I don't want to use "$" to set a fixed range, because then every time when the table is extended with one more column, the range will have to be re-drawn, and this can be very tedious.

I don't want to use offset() plus counta() either, because the range of data I need does not grow as the table increases its size, but it has a fixed length of 10.

I cannot easily have some search criteria for the year "2016", because there are several tables in parallel (for example another table with the same dimension, starting from column S), and looking for "2016" in a certain row might not always return the right column number.

Hope this is all clear. Could anybody suggest anything? VBA or formula are both welcome! Thanks a lot!

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!