OFFSET formula error when working with dynamic range graph

[greykitten]

Hi,

I'm struggling with a dynamic range graph formula. I can get it to work when the data is aligned vertically. But when I try to change the offset formula so that it reads the most recent 12 values horizontally, the formula returns an error message. It's a small problem in the formula where it instructs the formula to move how many rows and columns from the offset range. This is driving me nuts! Please help.


This is the file: https://hotfile.com/dl/222999248/4b51718/Dynamic_Range_Practice2.xlsx.html


Thank you,

grey kitten

 

[SirJB7]

Hi, greykitten!


In your uploaded file the chart data area isn't dynamically defined, it's fixed as:

=Sheet2!$A$1:$C$1,Sheet2!$A$5:$C$16


If for your vertical layout chart you use the formula (which I haven't checked):

=OFFSET(Sheet2!$A$1,COUNTA(Sheet2!$A:$A)-1,0,-MIN(chtLen,COUNTA(Sheet2!$A:$A)-1),1)

for your horizontal layout chart try this:

=OFFSET(Sheet2!$W$28,0,COUNTA(Sheet2!$28:$28)-1,1,-MIN(chtLen,COUNTA(Sheet2!$28:$28)-1))

and change the chtLen reference from:

D12

to:

I31


Hope it helps.


Regards!

 

[greykitten]

Thanks SirJB7! You're awesome!


greykitten

 

[SirJB7]

Hi, greykitten!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

PS: Something sounds a little strange to me... your original formula for the dynamic range definition it's very well constructed, but you couldn't make it get transposed? Reading the formula I'd have bet that you could.