1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with Dynamic Ranges

Discussion in 'Ask an Excel Question' started by Abernein, Jan 12, 2018.

  1. Abernein

    Abernein New Member

    Messages:
    4
    Hello,

    I have the following set of data (see attached file). Basically I have expenses for the past 6 years.

    Now, I would like to define a dynamic range that takes into account only the last 6 quarters. So, if I write Q2 2016 in cell A4 for instance, the range should automatically be set as I2:N2, which would then allow me to use this range in a chart to show the quarterly amount of expenses in the 6 quarters before Q2 2016. If I change A4 value to be Q4 2018, then the range would automatically be reset to S2:X2, etc.

    I was able to set a range for the past six quarters by defining a name MyRange and setting a formula for it to be =offset($b$2,0,counta(B2:Y2)-6,,6). However, this range is not dynamic. So I thought I could use the hlookup formula with it searching for the quarter and then using the offset function, but I get nowhere with this. So not sure what I am doing wrong or not doing at all.

    Can you help?
    Thanks a lot in advance
    Pascal

    Attached Files:

  2. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,596
    Try,

    1] In A4, enter : Q2 2016

    and, in A5 enter : Q4 2018

    2] In B4, formula copied down to B5

    =SUM(OFFSET($A$2,,MATCH($A4,$B$1:$Y$1,0)-1,,-6))

    Regards
    Bosco
    Thomas Kuriakose likes this.
  3. GraH - Guido

    GraH - Guido Active Member

    Messages:
    528
    something like this?

    EDIT: with chart now...

    Attached Files:

  4. Abernein

    Abernein New Member

    Messages:
    4
    Thanks guys, both approaches do exactly what I need!

Share This Page