Fill series in formula going column direction

[sidt87]

Hi,
I'm trying to copy data from sheet2 into sheet1 using the following formula

='sheet2'!$A1

and then dragging the formula all the way to column Z. I want to keep Column A the same but have it automatically fill the series of A1, A2, A3, and so on but it doesn't do that. Instead it'll copy the same exact information and if I remove the "$" sign, it'll change to ='sheet2'!A1, ='sheet2'!B1, ='sheet2'!C1 and so on.

Any idea how I would be able to solve this?

what I want
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]='sheet2'!$A1[/TD]
[TD]='sheet2'!$A2[/TD]
[TD]='sheet2'!$A3[/TD]
[TD]='sheet2'!$A4[/TD]
[TD]='sheet2'!$A5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

what's happening
[TABLE="width: 500"]
<tbody>[TR]
[TD]='sheet2'!$A1[/TD]
[TD]='sheet2'!$A1[/TD]
[TD]='sheet2'!$A1[/TD]
[TD]='sheet2'!$A1[/TD]
[TD]='sheet2'!$A1[/TD]
[/TR]
</tbody>[/TABLE]


Thanks!

 

[Fluff]

How about
=INDIRECT("sheet1!$A"&COLUMN(A1))

 

[Joe4]

Try:

=INDIRECT("Sheet2!A" & COLUMN())

EDIT: too slow!
Though you don't need to reference A1 in the COLUMN function, if you just want it to pull the column number from the current cell (whatever cell the formula is placed in).

 

[Rick Rothstein]

Assuming the following formula is placed in Column A, here is a non-Volatile function that I think should also work...

=INDEX(Sheet2!$A:$A,COLUMNS($A:A))

If you place the formula in a column other then A, change the red highlighted A's to that cell's column letter designation.

 

[sidt87]

Assuming the following formula is placed in Column A, here is a non-Volatile function that I think should also work...

=INDEX(Sheet2!$A:$A,COLUMNS($A:A))

If you place the formula in a column other then A, change the red highlighted A's to that cell's column letter designation.

This worked great thanks!

 

[sidt87]

Try:

=INDIRECT("Sheet2!A" & COLUMN())

EDIT: too slow!
Though you don't need to reference A1 in the COLUMN function, if you just want it to pull the column number from the current cell (whatever cell the formula is placed in).

Tried the formula and got #REF ! for a result.

 

[Joe4]

Tried the formula and got #REF ! for a result.

Do you actually have a sheet named "Sheet2"?
What cell were you putting the formula in?