Transpose data on diferent sheet.

[serge]

I need help with a formula that will transpose numbers from a column in one sheet to a row in a different sheet and as I keep scrolling the row to the right it would keep reading the column from that other sheet.

And also the other way around, Transpose a row to a column, I will need both way if possible for my work. Thank you.

The example below is on one sheet just to understand better.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		53	55	57	58	58	51	60	61	67	58	50	47	49
2
3	53
4	55
5	57
6	58
7	58
8	51
9	60
10	61
11	67
12	58
13	50
14	47
15	49
Sheet1

Thank you.
Serge.

 

[markmzz]

Try this:

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH><TH>N</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">53</TD><TD style="TEXT-ALIGN: center">55</TD><TD style="TEXT-ALIGN: center">57</TD><TD style="TEXT-ALIGN: center">58</TD><TD style="TEXT-ALIGN: center">58</TD><TD style="TEXT-ALIGN: center">51</TD><TD style="TEXT-ALIGN: center">60</TD><TD style="TEXT-ALIGN: center">61</TD><TD style="TEXT-ALIGN: center">67</TD><TD style="TEXT-ALIGN: center">58</TD><TD style="TEXT-ALIGN: center">50</TD><TD style="TEXT-ALIGN: center">47</TD><TD style="TEXT-ALIGN: center">49</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD></TR></TBODY></TABLE>Sheet2


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B1</TH><TD style="TEXT-ALIGN: left">=INDIRECT("Sheet1!"&ADDRESS(COLUMN()+1,ROW()))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">53</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center">55</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center">57</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: center">58</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: center">58</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: center">51</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: center">60</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: center">61</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: center">67</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: center">58</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: center">50</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: center">47</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: center">49</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: center">***</TD><TD style="TEXT-ALIGN: center">***</TD></TR></TBODY></TABLE>Sheet3


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>A3</TH><TD style="TEXT-ALIGN: left">=INDIRECT("Sheet2!"&ADDRESS(COLUMN(),ROW()-1))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Markmzz

 

[AlphaFrog]

Another method...

Row to column:
=INDEX(Sheet2!$1:$1,1,ROW(A2))

Column to row:
=INDEX(Sheet2!$A:$A,COLUMN(C1))

 

[serge]

Hi Markmzz,

Thank you very much for the formula it works perfect for the example I posted, but I need a little bit more of your help.

I tried to adjust your formula to my actual working sheet, without success.

They are in different columns and rows.

The data that need to be transposed start in

Sheet : Sheet71
and in cell : XQ16

How do I put that in the formula ??
Thank you.

 

[serge]

Thanks to you also AlphaFrog,

Can you look at Post # 4 and how your formula would be modified to work for those range ( For the one Column to Row ) ?

Thank you. Serge.

 

[AlphaFrog]

Try something like this..
=INDEX(Sheet71!$XQ:$XQ,COLUMN(P1))

If you INDEX column XQ, then XQ1 is index 1 and XQ2 is index 2, and XQ16 is index 16 etc...

So then you want a counter that changes as you drag the formula across columns. That's what COLUMN (??) does. It converts the column letter of a cell reference into a number. So COLUMN(P1) evaluates to the number 16. COLUMN(Q1) evaluates to the number 17 etc.

So....
=INDEX(Sheet71!$XQ:$XQ,COLUMN(P1))
=INDEX(Sheet71!$XQ:$XQ,16)
=Sheet71!$XQ16

=INDEX(Sheet71!$XQ:$XQ,COLUMN(Q1))
=INDEX(Sheet71!$XQ:$XQ,17)
=Sheet71!$XQ17

 

[markmzz]

Try this version 2.0 (now I use only Sheet1 that you can modified):

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B1</TH><TD style="TEXT-ALIGN: left">=OFFSET(Sheet1!$A$3,COLUMN()-COLUMN($B$1),0)</TD></TR></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>A3</TH><TD style="TEXT-ALIGN: left">=OFFSET(Sheet1!$B$1,0,ROW()-ROW($A$3))</TD></TR></TBODY></TABLE>


In your case try this:

=OFFSET(Sheet71!$XQ$16,COLUMN()-COLUMN($B$1),0)

Markmzz

 

[serge]

It work perfectly, thank you so very much AlphaFrog,

You save me a lot of headache, and also for the explanations of how the formula works, I understand it much better now.

But 1 question if I can : at what refer the 1 in P1 ?
P is for the reference of the 16 row but why the 1 ? because for the 17 is Q1, so the letter change but not the 1 ?

Just for me to understand what the number 1 do ?

Thank you.

 

[AlphaFrog]

In this case, the 1 is irrelevant. It could be 100 and it wouldn't matter.

We need a cell reference that changes as the formula is dragged across columns. We're only interested in the column letter of that cell reference.

This would work as well
=INDEX(Sheet71!$XQ:$XQ,COLUMN(P:P))

 

[serge]

AlphaFrog,

You've been great, thank you for the formula, your explanations and your time, I really do appreciate it.

Serge.