Formulas from columns to rows between data sheets

[shatan1984]

Hi im Using Excel 2010

I couldnt find anything on here so i thought id pop the question its probably really simple but i just cant seem to figure it out,

What it is i have two data sheets.

i want to display the content of data sheet1 on to data sheet2,

Which is easy i know how to do that how ever i want the columns to be displayed as rows and the rows as columns as i have quiet a bit of data to convert between the two, the formula im using is

Please Login or Register  to view this content.

but if i copy it across it gives me E37 where as i want D38 etc
after that i will need to do E37 across

so any help would be great full

Regards
Shane

[shg]

Welcome to the forum.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

[shatan1984]

Welcome to the forum.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

done that, but still no help yet

[romperstomper]

You haven't said where that formula is input, but you basically need either INDEX or INDIRECT:

=INDEX('Data sheet1'$1:$1048576,column(),row())
for example, or:
=INDIRECT("'Data sheet1'R"&column()&"C"&row(),0)

[Marcol]

See this workbook
=IFERROR(INDEX(Sheet1!$A$2:$G$20,COLUMN(A$1),ROW($A1)),"")

Or you could use the array formula TRANSPOSE()

Hope this helps

[EDIT]
Oops! Sorry RS, you beat me to it again!

[shatan1984]

You haven't said where that formula is input, but you basically need either INDEX or INDIRECT:

=INDEX('Data sheet1'$1:$1048576,column(),row())
for example, or:
=INDIRECT("'Data sheet1'R"&column()&"C"&row(),0)

sorry i was entering it straight into the cell

[shatan1984]

See this workbook
=IFERROR(INDEX(Sheet1!$A$2:$G$20,COLUMN(A$1),ROW($A1)),"")

Or you could use the array formula TRANSPOSE()

Hope this helps

[EDIT]
Oops! Sorry RS, you beat me to it again!

Thanks but that doesnt seem to work,

im entering the data in Cell B5 to Y5 on sheet 2 and the data is coming from Cell D30 to D53 on sheet 1....

[romperstomper]

sorry i was entering it straight into the cell

I meant which cell it was in - you may need to adjust the ROW() and COLUMN() parts depending on where your data starts on the data sheet compared to where your output is on the second sheet. (my formulas assume same locations on both).

[Marcol]

Try this
=IFERROR(INDEX(Sheet1!$D$30:$E$53,COLUMN(A$1),ROW($A1)),"")

This will handle two Columns of data, if you only need one column then change E to D, similarly if you have more columns to transpose, E gets changed (increased) accordingly.