Copy data from a column to a row

[jsjoblom]

I have a sheet that caculates data in columns A1-A100. I need to referance this caculated data in another sheet but in rows. I know that using the =A1 then draging down will referance properly but I want to drag it across the row. I have tried to copy and use the paste special, transpose but I get #REF! error. I tried paste special, values and transpose but that did not work. I do not want to copy the formula just the data it returns. Any help would be greatly appreciated

 

[njimack]

2 methods:
Enter the following into A1 of Sheet2 and then copy across as far as CV1:

=INDEX(Sheet1!$A$1:$A$100,COLUMN())

Select A1:CV1 of Sheet2, enter the following array formula, using ctrl+shift+enter:

=TRANSPOSE(Sheet1!A1:A100)

Excel Workbook
	A
1	$A$1
2	$A$2
3	$A$3
4	$A$4
5	$A$5
6	$A$6
7	$A$7
8	$A$8
9	$A$9
10	$A$10
Sheet1

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	$A$1	$A$2	$A$3	$A$4	$A$5	$A$6	$A$7	$A$8	$A$9	$A$10
2	$A$1	$A$2	$A$3	$A$4	$A$5	$A$6	$A$7	$A$8	$A$9	$A$10
Sheet2

 

[jsjoblom]

Excellent!! that looks good. Thank you very much!