Transpose column on one sheet to row on another, with linking

Question

I have a column of data in say A1:A10 in worksheet A. In worksheet B I have a range for data from A1:J1. I need the data from the column in worksheet A to be transposed into the data range in worksheet B. I can Paste Special and paste in the values but I need the information in worksheet A to update automatically that in worksheet B.

Any help or advice is appreciated.

Answer 1

Please select A1:J1 in worksheet B and enter:

=TRANSPOSE('worksheet A'!A1:A10)  

with Ctrl+Shift+Enter.

Answer 2

Copy this:

=INDEX(A!$A$1:$A$10, COLUMN())

into your cells A1 through J1 in worksheet B (so that all cells contain the exact same formula).

Basically, the INDEX function grabs a specified element out of your vertical array A1:A10 in worksheet A (the worksheet name is specified before the exclamation point). Which element to choose is given by the second parameter. Here, we simply use the COLUMN() function, which returns 1 for column A, 2 for column B and so forth...

EDIT:

If you want to use a different data column as the source of your data, simply replace the A!$A$1:$A$10 with anything else you like, e.g. Sheet3!$C$10:$C$23.

To place the copied data somewhere differently, copy the formula above into all the target cells, wherever they may be. BUT: If the target cells do not start in column A, you need to subtract an offset from the number returned by COLUMN(), so that you pick element 1 in your first target cell. So, for example if you want to place the data into cells J5 through S5, you will need to copy this formula into each cell:

=INDEX(A!$A$1:$A$10, COLUMN() - 9)

This is because COLUMN() returns 10 for column J, but you want that cell to contain element 1 of your source data column, so we subtract 9 to get from 10 to 1. In column K, COLUMN() will return 11, subtracting 9 yields 2, so INDEX(...) will then return the second data element for that cell, and so on.