Linking data housed in columns to rows

[Jturek]

Hey you guys-

I have a column of values on a separate spreadsheet (same workbook) that I need to link to a series of rows. Since there is a lot of data was wondering if there was a formula I could drag across the destination rows to link to the column's changing values.

Any help is appreciated!

Thanks!

 

[excelsishya]

Hi,

if data setup in 1 sheet is arranged like this

Sheet1

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Calibri,Arial; font-size: 11pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>A</td> <td>B</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>data1</td> <td>data2</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td style="text-align: right;">1</td> <td>Jan</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td style="text-align: right;">2</td> <td>feb</td></tr></tbody></table>

Then you can link column data into row data in another sheet

using transpose array function

Sheet2

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Calibri,Arial; font-size: 11pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>C</td> <td>D</td> <td>E</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>data1</td> <td style="text-align: right;">1</td> <td style="text-align: right;">2</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td>data2</td> <td>Jan</td> <td>feb</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); font-family: Arial; color: rgb(0, 0, 0); font-size: 10pt;"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>C3</td> <td>{=TRANSPOSE(Sheet1!$A$2:$B$4)}</td></tr> <tr> <td>D3</td> <td>{=TRANSPOSE(Sheet1!$A$2:$B$4)}</td></tr> <tr> <td>E3</td> <td>{=TRANSPOSE(Sheet1!$A$2:$B$4)}</td></tr> <tr> <td>C4</td> <td>{=TRANSPOSE(Sheet1!$A$2:$B$4)}</td></tr> <tr> <td>D4</td> <td>{=TRANSPOSE(Sheet1!$A$2:$B$4)}</td></tr> <tr> <td>E4</td> <td>{=TRANSPOSE(Sheet1!$A$2:$B$4)}</td></tr></tbody></table></td></tr> <tr> <td>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</td></tr></tbody></table>

 

[Jturek]

Awesome! Thanks once again!

 

[T. Valko]

Hey you guys-

I have a column of values on a separate spreadsheet (same workbook) that I need to link to a series of rows. Since there is a lot of data was wondering if there was a formula I could drag across the destination rows to link to the column's changing values.

Any help is appreciated!

Thanks!

Maybe this...

Suppose you have this data on Sheet1:

Book1
	A
2	1
3	2
4	3
5	4
6	5
Sheet1

And you want it to appear like this on Sheet2:

Book1
	A	B	C	D	E
2	1	2	3	4	5
Sheet2

Enter this formula on Sheet2 in cell A2 and copy across:

=INDEX(Sheet1!$A2:$A6,COLUMNS($A2:A2))