Formula Solution

[shemayisroel]

Hi,


The below table in Column A has samples in it, what I want to do is have a formula sokution in Row 1 from B:F that will pick up the values in Column A.

Example in D1 it would be =A3 but I would not be able to copy it across, basically I'm looking for a formula solution that is similar to paste special transpose. Hope thats clear enough.

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Sample 1</TD><TD style="COLOR: #0000ff">Sample 1</TD><TD style="COLOR: #0000ff">Sample 2</TD><TD style="COLOR: #0000ff">Sample 3</TD><TD style="COLOR: #0000ff">Sample 4</TD><TD style="COLOR: #0000ff">Sample 5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Sample 2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Sample 3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Sample 4</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Sample 5</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

 

[c_m]

Try:

<title>Excel Jeanie HTML</title><table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td>sample 1</td> <td>sample 1</td> <td>sample 2</td> <td>sample 3</td> <td>sample 4</td> <td>sample 5</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td>sample 2</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td>sample 3</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td>sample 4</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td>sample 5</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>B7</td> <td>=INDEX($A$7:$A$11,COLUMNS($A$7:B7)-COLUMN($B$7)+1)</td></tr></tbody></table></td></tr></tbody></table>
Drag it across.
HTH

 

[mikerickson]

How about an array formula?
{=TRANSPOSE(A1:A5)}

This needs to be confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac)

 

[c_m]

for using TRANSPOSE, you will first have to select B1:F1 (5 columns exactly), then press = and paste TRANSPOSE(A1:A5) confirmed with CSE

 

[Aladin Akyurek]

B1, copied across...

=INDEX($A$1:$A$5,COLUMNS($B1:B1))

would also do.

 

[shemayisroel]

Thanks c_m, I can definately use your solution.

Thanks mikerickson but I found the solution a little tweaky but have stored it as I'm sure I'll be able to use it for future solution.

And of course thanks Aladin, I ended up using your solution.

Thanks again to all, much appreciated.