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).


    [Blocked Image: https://www.excelforum.com/attachments/excel-programming-vba-macros/565266d1520872344-get-data-from-a-fixed-length-of-range-but-the-range-is-dynamic-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!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!