Trying to add from one sheet to another without clicking on each cell.
EX:
Sheet 1 Cell A1 = Sheet 2 Cell A1
Sheet 1 Cell A2 = Sheet 2 Cell A20
Sheet 1 Cell A3 = Sheet 2 Cell A40 etc etc.
How can i do this, to drag down on sheet 1 so I get the above results on
sheet 2.
Is there a formula to add lines not quantity?

Thanks

Use this in A1 of Sheet1
=INDIRECT("sheet2!A"&(((ROW()-1)*20)+1))

It will give you A1, A21,A41 from Sheet2 when copied down..

If you really want A1,A20,A40 then
use
=Sheet1!A2 in A1 of sheet1
and
=INDIRECT("sheet2!A"&((ROW()-1)*20))
in A2 and copy down


"dpal" wrote:

Trying to add from one sheet to another without clicking on each cell.
EX:
Sheet 1 Cell A1 = Sheet 2 Cell A1
Sheet 1 Cell A2 = Sheet 2 Cell A20
Sheet 1 Cell A3 = Sheet 2 Cell A40 etc etc.
How can i do this, to drag down on sheet 1 so I get the above results on
sheet 2.
Is there a formula to add lines not quantity?

Thanks

Sheeloo not quite clear.
What is the A for after Sheet 2!. Do I type this in. Also do I put a Row
number in the () after row.
I tried with and it comes up ## REf. and without it comes to 0 but when I
drag down it doesn't seem to work. Is it me?

Thanks for your help.

"Sheeloo" wrote:

Use this in A1 of Sheet1
=INDIRECT("sheet2!A"&(((ROW()-1)*20)+1))

It will give you A1, A21,A41 from Sheet2 when copied down..

If you really want A1,A20,A40 then
use
=Sheet1!A2 in A1 of sheet1
and
=INDIRECT("sheet2!A"&((ROW()-1)*20))
in A2 and copy down


"dpal" wrote:

Trying to add from one sheet to another without clicking on each cell.
EX:
Sheet 1 Cell A1 = Sheet 2 Cell A1
Sheet 1 Cell A2 = Sheet 2 Cell A20
Sheet 1 Cell A3 = Sheet 2 Cell A40 etc etc.
How can i do this, to drag down on sheet 1 so I get the above results on
sheet 2.
Is there a formula to add lines not quantity?

Thanks

Enter it exactly as shown...
=INDIRECT("sheet2!A"&(((ROW()-1)*20)+1))

Let us assume you entered it in A3 on Sheet1
ROW() will give you 3 (third row)
(ROW()-1)*20 will give your 40 ... 3-1 multiplied by 20
"sheet2!A"&(((ROW()-1)*20)+1) will give you the string Sheett2!A40

So Indirect() will get the address Sheett2!A40 and will return the value
there...

"dpal" wrote:

Sheeloo not quite clear.
What is the A for after Sheet 2!. Do I type this in. Also do I put a Row
number in the () after row.
I tried with and it comes up ## REf. and without it comes to 0 but when I
drag down it doesn't seem to work. Is it me?

Thanks for your help.

"Sheeloo" wrote:

Use this in A1 of Sheet1
=INDIRECT("sheet2!A"&(((ROW()-1)*20)+1))

It will give you A1, A21,A41 from Sheet2 when copied down..

If you really want A1,A20,A40 then
use
=Sheet1!A2 in A1 of sheet1
and
=INDIRECT("sheet2!A"&((ROW()-1)*20))
in A2 and copy down


"dpal" wrote:

Trying to add from one sheet to another without clicking on each cell.
EX:
Sheet 1 Cell A1 = Sheet 2 Cell A1
Sheet 1 Cell A2 = Sheet 2 Cell A20
Sheet 1 Cell A3 = Sheet 2 Cell A40 etc etc.
How can i do this, to drag down on sheet 1 so I get the above results on
sheet 2.
Is there a formula to add lines not quantity?

Thanks

Got it
Thanks

"Sheeloo" wrote:

Enter it exactly as shown...
=INDIRECT("sheet2!A"&(((ROW()-1)*20)+1))

Let us assume you entered it in A3 on Sheet1
ROW() will give you 3 (third row)
(ROW()-1)*20 will give your 40 ... 3-1 multiplied by 20
"sheet2!A"&(((ROW()-1)*20)+1) will give you the string Sheett2!A40

So Indirect() will get the address Sheett2!A40 and will return the value
there...

"dpal" wrote:

Sheeloo not quite clear.
What is the A for after Sheet 2!. Do I type this in. Also do I put a Row
number in the () after row.
I tried with and it comes up ## REf. and without it comes to 0 but when I
drag down it doesn't seem to work. Is it me?

Thanks for your help.

"Sheeloo" wrote:

Use this in A1 of Sheet1
=INDIRECT("sheet2!A"&(((ROW()-1)*20)+1))

It will give you A1, A21,A41 from Sheet2 when copied down..

If you really want A1,A20,A40 then
use
=Sheet1!A2 in A1 of sheet1
and
=INDIRECT("sheet2!A"&((ROW()-1)*20))
in A2 and copy down


"dpal" wrote:

Trying to add from one sheet to another without clicking on each cell.
EX:
Sheet 1 Cell A1 = Sheet 2 Cell A1
Sheet 1 Cell A2 = Sheet 2 Cell A20
Sheet 1 Cell A3 = Sheet 2 Cell A40 etc etc.
How can i do this, to drag down on sheet 1 so I get the above results on
sheet 2.
Is there a formula to add lines not quantity?

Thanks