How to transpose data (Row to column) without losing the cell link

[Maxim]

If I transpose data (horizontal row to vertical column)from one sheet to
another with the "copy" and "Paste Special" command using "transpose", the
programme only gives the values. I need to keep the link to the original
sheet to ensure that if any data is changed in the original sheet (row) that
it changes in the column on the new sheet. I have tried the "Transpose"
@-function unsucesfully!!

 

[Gary''s Student]

Say we have data in Sheet1, row 1 and we want that to appear in Sheet2 column
1, but keep it linked.

In Sheet2 A1, enter:

=INDIRECT("Sheet1!"&CHAR(64+ROW())&"1") and copy down

 

[Gary''s Student]

Use this instead:

=INDIRECT("Sheet1!"&ADDRESS(1,ROW()))

the first posted equation only works for 26 items.

 

[Ron Rosenfeld]

If I transpose data (horizontal row to vertical column)from one sheet to
another with the "copy" and "Paste Special" command using "transpose", the
programme only gives the values. I need to keep the link to the original
sheet to ensure that if any data is changed in the original sheet (row) that
it changes in the column on the new sheet. I have tried the "Transpose"
@-function unsucesfully!!

You have in sheet1!F1:H1 that you wish to appear on some other sheet in A1:A3.

You could use a formula of the type:

=IF(ROWS($1:1)>COLUMNS(Sheet1!$F$1:$H$1),"",INDEX(Sheet1!$F$1:$H$1,1,ROWS($1:1)))

in A1 and fill down as far as required. It will return blanks if you go too
far.

Adjust your references to your ROW range as required.
--ron