How to insert a formula on the reference of an Offset Formula

[Caucho]

i have a combination of sum, offset and match formula which is working well for one row but i need it to move down, so the formula is something like this:

=sum(offset($B$2,0,0,1,match($B$8,$B$8:$C$22))). This formula is on cell B9.

Now, on B1 to E1 i have got dates starting from 1-Jan-13 and on A9 to A12 i have dates again startin from 01-Jan-12.


What i want is to move the formula down to B10 and the offset reference to start from the next date, or from C instead of B as the formula above. So the question is how i make the reference of the offset formula into a formula that makes the starting poing of the offset move?

 

[NARAYANK991]

Hi ,


I am not sure about your formula ; the MATCH function will not work over a multi-row , multi-column range. You can have MATCH(B8,B9:B22) or MATCH(B8,C8:C22) or any other version which spans several columns but only one row , such as MATCH(B8,B9:K9).


Secondly , if your formula is in B9 , and you are trying to MATCH the contents of B8 over the range B8:C22 , what is it that you want to do ?


Can you please explain what your requirement is , rather than ask for others to troubleshoot your formula , since there may be other ways of doing the same thing.


Narayan

 

[Faseeh]

Hi all,


I am not clear regarding the exact requirement as well as your formula seems to be creating circular reference as you are entering it in B9 and you are referring to range B8:C22 in your MATCH() formula so it is certain to give you problem.


You can carry the column by using:


'=ADDRESS(2,ROW(A2))`


if you enter it in certain cell and drag it down you can see the result.


for your formula reference use:


=SUM(OFFSET(INDIRECT(ADDRESS(2,ROW(A2))),0,0,1,MATCH($B$8,$B$8:$C$22)))


...but still i am doubtful regarding that part $B$8:$C$22 because it will create a circular reference. .


If OP Can you upload the sample file??


Regards,