COPY RIGHT AND TRANSPOSE
 

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?

COPY RIGHT AND TRANSPOSE
 

Pam M wrote:
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)

COPY RIGHT AND TRANSPOSE
 

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

Entered in SheetB A1 and dragged across.


Gord Dibben MS Excel MVP



On Tue, 7 Apr 2009 13:09:02 -0700, Pam M
wrote:

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?