Transpose of data from horizontal to vertical

[Hrishi]

Hello, I have huge data in one row. I want to create a column with same data and which will be linked to the original horizontal data so that whenever I change in horizontal data, column data will also change. e.g.

row	1	2	3	4	5	column
						1
						2
						3
						4
						5

<tbody>
</tbody>

Column data shall be linked to original horizontal data. It is very tedious to link each cell. Plz help

Regards

 

[Eric W]

You can use OFFSET or INDEX to make a reference to another location in the workbook.

	A	B	C	D	E	F	G	H
1	row	1	2	3	4	5
2
3	Column
4	1
5	2
6	3
7	4
8	5
9	0
10	0

<tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula A4 =INDEX($B$1:$Z$1,ROW()-3) <tbody> </tbody>

<tbody>
</tbody>

In this example, I use $A$1:$Z$1 as the range (change to match your sheet, with Z changing to the maximum column you use), and ROW() to decide the column. I subtract 3 from ROW since I start the column in row 4.

I used INDEX instead of OFFSET since it's not volatile. Also note that rows 9 and 10 have a 0 in it, and the source row is empty. You can change the formula to

=INDEX($B$1:$Z$1,ROW()-3)&""

to avoid that, but it changes the cell to text.

Hope this helps.

 

[Hrishi]

Hi, yes It working. actually I wanted to transform horizontal data in to the various vertical columns (making a table) so that later I can use that table as pivot table. Hop this arrangement you have taught to me remains nonvolatile even in upivot table.
Thanks you for your help.

 

[Eric W]

You're welcome.