Columns to Rows?

  • Thread starter Thread starter Paul
  • Start date Start date
P

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.
 
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.
 
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))
 
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.
 
Back
Top