drag and drop formulas

I have a problem. This is what my sheet looks like:
sheet1
In column A is the name of the company. in column B the name of the company
and In column c is the monthly return of the company. I'm interested in the
compounded half year return. In a new worksheet (sheet2)I want the company
name in column A, the compounded half year return (first six months) in
column b and the compounded half year return (last six months) in column c.
The formula for the compounded half year return(first six months in cell b2
is:
=((1+'sheet1'!a2)*(1+'sheet1'!a3)*(1+'sheet1'!a4)*(1+'sheet1'!a5)*(1+'sheet1'!a6)*(1+'sheet1'!a7))-1

The formula for the compounded half year return(last six months in cell c2 is:

=((1+Sheet1!A8)*(1+Sheet1!A9)*(1+Sheet1!A10)*(1+Sheet1!A11)*(1+Sheet1!A12)*(1+Sheet1!A13))-1

I want to drag down this formula so that the formula for cell b3 is

=((1+Sheet1!A14)*(1+Sheet1!A15)*(1+Sheet1!A16)*(1+Sheet1!A17)*(1+Sheet1!A18)*(1+Sheet1!A19))-1

and so on.

I hope my question is clear enough so that somebody can help me out.

Thanx and regards

Bram

Use a helper column C, otherwise the formula will be very very long:
In C2: =2+(CELL("row";C2)-2)*12

In B2: A2 A3
=(1+INDIRECT("Sheet1!A"&C2)*(1+INDIRECT("Sheet1!A"&C2+1))) ... and so on
A7
until INDIRECT("Sheet1!A"&C2+5)


and fill down!

Regards,
Stefi

„Bram” ezt �*rta:

> I have a problem. This is what my sheet looks like:
> sheet1
> In column A is the name of the company. in column B the name of the company
> and In column c is the monthly return of the company. I'm interested in the
> compounded half year return. In a new worksheet (sheet2)I want the company
> name in column A, the compounded half year return (first six months) in
> column b and the compounded half year return (last six months) in column c.
> The formula for the compounded half year return(first six months in cell b2
> is:
> =((1+'sheet1'!a2)*(1+'sheet1'!a3)*(1+'sheet1'!a4)*(1+'sheet1'!a5)*(1+'sheet1'!a6)*(1+'sheet1'!a7))-1
>
> The formula for the compounded half year return(last six months in cell c2 is:
>
> =((1+Sheet1!A8)*(1+Sheet1!A9)*(1+Sheet1!A10)*(1+Sheet1!A11)*(1+Sheet1!A12)*(1+Sheet1!A13))-1
>
> I want to drag down this formula so that the formula for cell b3 is
>
> =((1+Sheet1!A14)*(1+Sheet1!A15)*(1+Sheet1!A16)*(1+Sheet1!A17)*(1+Sheet1!A18)*(1+Sheet1!A19))-1
>
> and so on.
>
> I hope my question is clear enough so that somebody can help me out.
>
> Thanx and regards
>
> Bram
>

Thank you Stefi!!!

"Stefi" wrote:

> Use a helper column C, otherwise the formula will be very very long:
> In C2: =2+(CELL("row";C2)-2)*12
>
> In B2: A2 A3
> =(1+INDIRECT("Sheet1!A"&C2)*(1+INDIRECT("Sheet1!A"&C2+1))) ... and so on
> A7
> until INDIRECT("Sheet1!A"&C2+5)
>
>
> and fill down!
>
> Regards,
> Stefi
>
> „Bram” ezt �*rta:
>
> > I have a problem. This is what my sheet looks like:
> > sheet1
> > In column A is the name of the company. in column B the name of the company
> > and In column c is the monthly return of the company. I'm interested in the
> > compounded half year return. In a new worksheet (sheet2)I want the company
> > name in column A, the compounded half year return (first six months) in
> > column b and the compounded half year return (last six months) in column c.
> > The formula for the compounded half year return(first six months in cell b2
> > is:
> > =((1+'sheet1'!a2)*(1+'sheet1'!a3)*(1+'sheet1'!a4)*(1+'sheet1'!a5)*(1+'sheet1'!a6)*(1+'sheet1'!a7))-1
> >
> > The formula for the compounded half year return(last six months in cell c2 is:
> >
> > =((1+Sheet1!A8)*(1+Sheet1!A9)*(1+Sheet1!A10)*(1+Sheet1!A11)*(1+Sheet1!A12)*(1+Sheet1!A13))-1
> >
> > I want to drag down this formula so that the formula for cell b3 is
> >
> > =((1+Sheet1!A14)*(1+Sheet1!A15)*(1+Sheet1!A16)*(1+Sheet1!A17)*(1+Sheet1!A18)*(1+Sheet1!A19))-1
> >
> > and so on.
> >
> > I hope my question is clear enough so that somebody can help me out.
> >
> > Thanx and regards
> >
> > Bram
> >

You're welcome! Thanks for the feedback!
Stefi


„Bram” ezt �*rta:

> Thank you Stefi!!!
>
> "Stefi" wrote:
>
> > Use a helper column C, otherwise the formula will be very very long:
> > In C2: =2+(CELL("row";C2)-2)*12
> >
> > In B2: A2 A3
> > =(1+INDIRECT("Sheet1!A"&C2)*(1+INDIRECT("Sheet1!A"&C2+1))) ... and so on
> > A7
> > until INDIRECT("Sheet1!A"&C2+5)
> >
> >
> > and fill down!
> >
> > Regards,
> > Stefi
> >
> > „Bram” ezt �*rta:
> >
> > > I have a problem. This is what my sheet looks like:
> > > sheet1
> > > In column A is the name of the company. in column B the name of the company
> > > and In column c is the monthly return of the company. I'm interested in the
> > > compounded half year return. In a new worksheet (sheet2)I want the company
> > > name in column A, the compounded half year return (first six months) in
> > > column b and the compounded half year return (last six months) in column c.
> > > The formula for the compounded half year return(first six months in cell b2
> > > is:
> > > =((1+'sheet1'!a2)*(1+'sheet1'!a3)*(1+'sheet1'!a4)*(1+'sheet1'!a5)*(1+'sheet1'!a6)*(1+'sheet1'!a7))-1
> > >
> > > The formula for the compounded half year return(last six months in cell c2 is:
> > >
> > > =((1+Sheet1!A8)*(1+Sheet1!A9)*(1+Sheet1!A10)*(1+Sheet1!A11)*(1+Sheet1!A12)*(1+Sheet1!A13))-1
> > >
> > > I want to drag down this formula so that the formula for cell b3 is
> > >
> > > =((1+Sheet1!A14)*(1+Sheet1!A15)*(1+Sheet1!A16)*(1+Sheet1!A17)*(1+Sheet1!A18)*(1+Sheet1!A19))-1
> > >
> > > and so on.
> > >
> > > I hope my question is clear enough so that somebody can help me out.
> > >
> > > Thanx and regards
> > >
> > > Bram
> > >