autofilling a column based on values on a row

Hi all, Im wondering if it is possible to autofill something like this:


(C34) =IF($B$6=B34;0;SUMPRODUCT($D$2:INDEX($D$2:$D$31;$B$6-B34);$E$2:INDEX($E$2:$E$31;$B$6-B34)))
(C35) =IF($B$6=B35;0;SUMPRODUCT($D$2:INDEX($D$2:$D$31;$B$6-B35);$F$2:INDEX($F$2:$F$31;$B$6-B35)))
(C36) =IF($B$6=B36;0;SUMPRODUCT($D$2:INDEX($D$2:$D$31;$B$6-B36);$G$2:INDEX($G$2:$G$31;$B$6-B36)))


every next column needs a data figure from the next row. So I have to change by hand the columnnumber in each row.
transposing the thing wont matter cuz then somewhere else will be the same problem.
Is there a way to do this cuz the dollarsign method lacks the solution.

(ColomnE) (columnF) (ColumnG)
nqx+1 nqx+2 nqx+3
0,00119119 0,001299109 0,001417813
0,001297561 0,001415972 0,001546225
0,001414285 0,001544216 0,001686846



Tnx, Goofer

Originally Posted by dilipandey

Hi Goofer,

I guess you can use Offset function here to change the range.. can you upload a sample workbook along with your expected output. Thanks.


Regards,
DILIPandey
<click on below * if this helps>

Hey tnx for ur reaction !

Please Login or Register  to view this content.

its the sheet VZ Totaal the C 34 till C54 and F34 F 54 U see that I put a diff column number in every row.

Would be nice to see if i know a solution to this general 'problem'
cause i use an Index function which depends of the lookup value in combination of a sumproduct funtion transposing doesnt really work

Originally Posted by dilipandey

Formula:

Please Login or Register  to view this content.

used above formula in column C, see attached

Attachment 239987

Regards,
DILIPandey
<click on below * if this helps>

Tnx alot !! Added that technique to my knowledge