Transposing Column Headers into Rows

[Cyclops755]

Searched around for a bit, and found a few similar questions, but none with answers that seemed to solve my problem.

I'm trying to take the column headers of a table on one sheet and make them row entries on a table on a different sheet, and do it in such a way that if I add columns to the source table, its header will appear as a new row item automatically. I've tried using different INDEX/MATCH statements, and have given the headers their own named range. I just can't seem to get it cooperate. I'm sure there's a simple way to do this, I just can't seem to find it!

Thanks go out in advance

 

[cgcamal]

Hi Cyclops,

You can try with Transpose function like this
:
In "Headers Sheet" you have the headers as follow:

Excel Workbook
	A	B	C	D
1	Header 1	Header 2	Header 3
2
Headers Sheet

In "Headers to row" sheet you introduce Transpose formula as follow:
(array formula, Select range A1:A10 and do Ctrl+Shift+Enter)

Excel Workbook
	A
1	Header 1
2	Header 2
3	Header 3
4	Header 4
5	0
6	0
7	0
8	0
9	0
10	0
Headers to rows

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter these manually yourself
The thing is that you need to select a larger range than the actually is used. I mean, in sheet 1 there
are only 3 headers (A1:C1), but in sheet 2 I introduce in the formula a larger range (A1:J1), that contain
10 columns, you can select many more as you want. Then when you introduce more headers in Sheet1 (like Header 4),
those headers will appear as rows in Sheet2


Is an idea, hope this helps

Regards

 

[Cyclops755]

That was precisely my train of thought, and the first thing I tried. But whenever I do that, I end up with:

<table border="0" cellpadding="0" cellspacing="0" width="148"><col style="mso-width-source:userset;mso-width-alt:5412;width:111pt" width="148"> <tbody><tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;width:111pt; font-size:10.0pt;color:white;font-weight:700;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Arial;border:.5pt solid #95B3D7; background:#4F81BD;mso-pattern:#4F81BD none" height="17" width="148">NewTableHeader</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border-top:none;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DBE5F1;mso-pattern:#DBE5F1 none" height="17">(OldTableHeader1)</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border-top:none;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7" align="right" height="17">0</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border-top:none;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DBE5F1;mso-pattern:#DBE5F1 none" align="right" height="17">0</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border-top:none;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7" align="right" height="17">0</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border-top:none;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DBE5F1;mso-pattern:#DBE5F1 none" align="right" height="17">0</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border-top:none;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7" align="right" height="17">0</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border-top:none;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DBE5F1;mso-pattern:#DBE5F1 none" align="right" height="17">0</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border-top:none;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7" align="right" height="17">0</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border-top:none;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DBE5F1;mso-pattern:#DBE5F1 none" align="right" height="17">0</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">0</td> </tr> </tbody></table>

 

[Cyclops755]

::Bump::

 

[Cyclops755]

bump..