Columns to Rows?

[Paul]

Within a workbook I have a tab that collects data from a
digital control system and stores it in cell columns.
How can I send this data to another tab within the same
workbook which displays the data in cell rows?
Seems simple enough but can't seem to figure it out.
Thanks in advance.

 

[Aladin Akyurek]

Let row 1 on Sheet2 house the readings and we want them in column A on
Sheet1 (Horizontal to vertical transposition)...

In A1 on Sheet1 enter & copy down:

=INDEX(Sheet2!$1:$1,ROW()-ROW($A$1)+1)

The ROW($A$1) bit indicates that this formula is anchored to the first cell
where it is entered.

Let column A on Sheet2 house the readings and we want them in row 1 on
Sheet1 (Vertical to horizontal transposition)...

In A1 on Sheet1 enter & copy across:

=INDEX(Sheet2!$A:$A,COLUMN()-COLUMN($A$1)+1)

The COLUMN($A$1) bit indicates that this formula is anchored to the first
cell where it is entered.

 

[Arvi Laanemets]

Hi

Into cell A1 on another workbook enter the formula
=OFFSET(Sheet1!$A$1,COLUMN()-1,ROW()-1)
and copy to range you need.

When the source range isn't fixed, then use the formula
=IF(OFFSET(Sheet1!$A$1,COLUMN()-1,ROW()-1)="","",OFFSET(Sheet1!$A$1,COLUMN()
-1,ROW()-1))

 

[Ken Wright]

You could also just use the TRANSPOSE function, which must be array entered.

Assuming you had data in say A1:A10, you could select say A15:J15, type
=TRANSPOSE(A1:A10) hit CTRL+SHIFT+ENTER and it will tranpose the data but remain
linked to it.