COPY RIGHT AND TRANSPOSE

  • Thread starter Thread starter Pam M
  • Start date Start date
P

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?
 
Pam said:
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?
Click to expand...


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

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

Entered in SheetB A1 and dragged across.


Gord Dibben MS Excel MVP
 
Back
Top