COPY RIGHT AND TRANSPOSE

[Pam M]

I am building a perpetual workbook where sheet B will reading sheet A via
formula. Sheet B is a transposition of sheet A rows to columns, so my many
rows of data from A will become many columns of data on B. When you copy
down, excel automatically keeps the column reference but changes the row
reference. If I am copying my formula to the right, is there any way for
excel to keep the row reference, but change the column reference for ease in
building this workbook?

 

[Glenn]

I am building a perpetual workbook where sheet B will reading sheet A via
formula. Sheet B is a transposition of sheet A rows to columns, so my many
rows of data from A will become many columns of data on B. When you copy
down, excel automatically keeps the column reference but changes the row
reference. If I am copying my formula to the right, is there any way for
excel to keep the row reference, but change the column reference for ease in
building this workbook?

Try a variation of this formula, which will transpose your entire sheet:

=INDIRECT("SheetA!R"&COLUMN()&"C"&ROW(),FALSE)

 

[Gord Dibben]

=INDIRECT("SheetA!A"&COLUMN(A1))

Entered in SheetB A1 and dragged across.


Gord Dibben MS Excel MVP