Dragging Formula from alternate cells

[wendy21]

In sheet 1 Cell d11 I want to add from sheet 2 the sum of cells d18 and e18, I then want to drag this formula so that in Sheet 1 Cell e11 it shows the sum of Sheet 2 cells f18 and g18. Is there a way of doing this?

 

[hrayani]

=OFFSET(Sheet2!$D$18,0,ROW(1:1)-1)+OFFSET(Sheet2!$E$18,0,ROW(1:1)-1)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[hrayani]

I just realised that you want to move 2 columns forward i.e. from D:E to F:G
In said case try this

=OFFSET(Sheet2!$D$18,0,ROW(1:1)*2-2)+OFFSET(Sheet2!$E$18,0,ROW(1:1)*2-2)

 

[wendy21]

Thanks but when I drag the formula it tries to add e:f not f:g
=OFFSET('Funding Capacity'!E$18,0,ROW(1:1)*2-2)+OFFSET('Funding Capacity'!F$18,0,ROW(1:1)*2-2)
I've tried with and without the $ in front of D & E

 

[Fluff]

Another option, for 365

=SUM(INDEX(Sheet2!$D$18:$W$18,SEQUENCE(,2,COLUMNS($D11:D11)*2-1)))

 

[wendy21]

Excellent thank you that works.

 

[hrayani]

=OFFSET('Funding Capacity'!E$18,0,ROW(1:1)*2-2)+OFFSET('Funding Capacity'!F$18,0,ROW(1:1)*2-2)

this must be set to Column D + E as this is the formula in the first instance

Use this -this will add D18+E18 > when drag down it will plus F18+G18

=OFFSET('Funding Capacity'!$D$18,0,ROW(1:1)*2-2)+OFFSET('Funding Capacity'!$E$18,0,ROW(1:1)*2-2)

 

[Fluff]

Glad we could help & thanks for the feedback.