Help with Dynamic Ranges

[Abernein]

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

 

[bosco_yip]

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

 

[GraH - Guido]

something like this?

EDIT: with chart now...

 

[Abernein]

Thanks guys, both approaches do exactly what I need!