Making the reference of an offset formula change when its dragged down

[Caucho]

Hi, need help with this formula


To simplify imagine i have dates going from row B1 to L1 starting from 1-Jan-13 to 10-Jan-13

And on row 2 i have some numbers from B2 all the way to L


On A3, i have dates again going down from 01-Jan-13 all the way to the 10th as well.


On, B3, i have the formula =sum(offset($b$2,0,0,1,match(B$1,$bB$1:$L$1,0))). Now if i drag the formula sideways, it will always add the number starting B2.


Now, i want to drag the formula down to B4, but now because A4 refers to the 2-Jan-13 i want the offset formula to start summing up from C2 instead of B2 because it must add the data from the 2nd day going forward.

 

[NARAYANK991]

Hi ,


Try this :


=IF(B$1<$A3,"",SUM(OFFSET(OFFSET($B$2,,ROW()-ROW($B$3)),,,,MATCH(B$1,OFFSET($B$1,,ROW()-ROW($B$3)):$L$1,0))))


in B3 ; copy it across and down.


Is it what you want ?


Narayan

 

[Faseeh]

Hi Caucho,


This one as well:


=IF(K$1<$A12,"",SUM(OFFSET(INDIRECT(ADDRESS(ROW(J$2),MATCH($A12,$B$1:$L$1,0)+1)),0,0,1,MATCH(K$1,$B$1:$L$1,0))))


Regards,

 

[Luke M]

Even shorter:

=SUM(OFFSET($B$2,,ROW($A1)-1,1,MATCH(B$1,$L$1:$BB$1,0)))


You just need to offset the starting cell by 1 everytime you go down a row, so we'll just use the natural col_offset argument to control that.