dragging formulas

I am having a problem dragging down a formula. Its easier for me to give an
example rather than describe the problem. Example: The formula im trying to
drag down is: c41+c87+c137+c184. If i drag this down i will get
c42+c88+c138+c185 in the next row but the formula i result i would like for
the drag down is d41+d87+d137+d184. Any thoughts on how i can correct this?

Offset the column number by the number of rows above the firstline of the drag,
ie, using c41+c87+c137+c184 in Row 9 and 'dragging' down would give

=OFFSET($C$41,0,ROW()-9)+OFFSET($C$87,0,ROW()-9)+OFFSET($C$137,0,ROW()-9)+OFFSET($C$184,0,ROW()-9)

and formula drag that downwards.

Originally Posted by rmarks

I am having a problem dragging down a formula. Its easier for me to give an
example rather than describe the problem. Example: The formula im trying to
drag down is: c41+c87+c137+c184. If i drag this down i will get
c42+c88+c138+c185 in the next row but the formula i result i would like for
the drag down is d41+d87+d137+d184. Any thoughts on how i can correct this?

Hi,

Try,

=OFFSET($A$1,40,ROW(A1)+1)+OFFSET($A$1,86,ROW(A1)+1)+OFFSET($A$1,136,ROW(A1)+1)+OFFSET($A$1,183,ROW(A1)+1)

Here, the number after the comma immediately after $A$1 in each OFFSET
function is one less than the row number of the cell added.

Regards,
B. R. Ramachandran

"rmarks" wrote:

> I am having a problem dragging down a formula. Its easier for me to give an
> example rather than describe the problem. Example: The formula im trying to
> drag down is: c41+c87+c137+c184. If i drag this down i will get
> c42+c88+c138+c185 in the next row but the formula i result i would like for
> the drag down is d41+d87+d137+d184. Any thoughts on how i can correct this?

The only way I know to get the results you want is to drag horizontally.
Then 'Cut' and 'Paste' in Column you tried to drag down.

TC

"rmarks" wrote:

> I am having a problem dragging down a formula. Its easier for me to give an
> example rather than describe the problem. Example: The formula im trying to
> drag down is: c41+c87+c137+c184. If i drag this down i will get
> c42+c88+c138+c185 in the next row but the formula i result i would like for
> the drag down is d41+d87+d137+d184. Any thoughts on how i can correct this?

That worked for me. Thank you very much!

"Bryan Hessey" wrote:

>
> Offset the column number by the number of rows above the firstline of
> the drag,
> ie, using c41+c87+c137+c184 in Row 9 and 'dragging' down would give
>
> =OFFSET($C$41,0,ROW()-9)+OFFSET($C$87,0,ROW()-9)+OFFSET($C$137,0,ROW()-9)+OFFSET($C$184,0,ROW()-9)
>
> and formula drag that downwards.
>
>
> rmarks Wrote:
> > I am having a problem dragging down a formula. Its easier for me to
> > give an
> > example rather than describe the problem. Example: The formula im
> > trying to
> > drag down is: c41+c87+c137+c184. If i drag this down i will get
> > c42+c88+c138+c185 in the next row but the formula i result i would like
> > for
> > the drag down is d41+d87+d137+d184. Any thoughts on how i can correct
> > this?
>
>
> --
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=486388
>
>

Thanks for the reply.

Originally Posted by rmarks

That worked for me. Thank you very much!

"Bryan Hessey" wrote:

>
> Offset the column number by the number of rows above the firstline of
> the drag,
> ie, using c41+c87+c137+c184 in Row 9 and 'dragging' down would give
>
> =OFFSET($C$41,0,ROW()-9)+OFFSET($C$87,0,ROW()-9)+OFFSET($C$137,0,ROW()-9)+OFFSET($C$184,0,ROW()-9)
>
> and formula drag that downwards.
>
>
> rmarks Wrote:
> > I am having a problem dragging down a formula. Its easier for me to
> > give an
> > example rather than describe the problem. Example: The formula im
> > trying to
> > drag down is: c41+c87+c137+c184. If i drag this down i will get
> > c42+c88+c138+c185 in the next row but the formula i result i would like
> > for
> > the drag down is d41+d87+d137+d184. Any thoughts on how i can correct
> > this?
>
>
> --
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=486388
>
>