traspose data with formulas

[Guest]

I have the following data in sheet 1

Column A
1
2
3
4
5

I need to transpose the data of sheet 1 to sheet 2 but I need to retain the
cell references so that whenever there is a change of data in sheet 1, sheet
2 is automatically updated.

In sheet 2, i need to get the data like this

Column
A B C D E
1 2 3 4 5.


If any of u have an answer, please post the reply.

 

[T. Valko]

Try this:

Assuming your data starts in Sheet1 in cell A1:

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

Copy across as needed.

 

[Guest]

Thanks for the reply. It worked!!!.


I have another query, the same data which is transposed needs to be
transposed into multiple rows, i.e

Column A

1
2
3
4
5
6
7
8
9
10


I need to transpose this data in Sheet 2 in multiple rows ie. for items
upto 1-4 in Row1 and 5-8 in Row 2 and 9-10 in Row 3.

Can you please let me know how this can be done.

 

[T. Valko]

Try this:

=INDEX(Sheet1!$A:$A,ROWS($1:1)*4-4+COLUMNS($A:A))

Copy across to a total of 4 cells then down as needed.

Note that based on your sample row 3 will return these values:

9...10...0...0

If you don't want the 0s try this formula:

=IF(INDEX(Sheet1!$A:$A,ROWS($1:1)*4-4+COLUMNS($A:A))=0,"",INDEX(Sheet1!$A:$A,ROWS($1:1)*4-4+COLUMNS($A:A)))