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

  • Thread starter Thread starter Maxim
  • Start date Start date
M

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!!
 
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
 
Use this instead:

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

the first posted equation only works for 26 items.
 
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!!
Click to expand...

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
 
Back
Top