Thread: Dynamic Charts

I am trying to do a dynamic chart, to capture movements in the last 12 months.

I am using the following formula

=OFFSET('Working Capital'!$B$1,COUNTA('Working Capital'!$B:$B)-12,0,12,1)

and it is not working.

I have attached the data if any one may tell me what am getting wrong.
Thank you.

I'd recommend the non-volatile Index/Match combo instead, but the reason it's not working is that you're having it look up by columns instead of by rows. The Offset version should be: =OFFSET('Working Capital'!$B3,0,COUNTA('Working Capital'!3:3)-13,1,12) and the Index/Match should be: =INDEX(B3:R3,COUNTA(B3:R3)-11):INDEX(B3:R3,MATCH(99^99,B3:R3)). Both of these would be for Rough Inventory and should be able to be dragged down for the ranges of the other rows.

Thank you darkyam! It works now!