Transposing Sums & autofill

[Reno]

on report1 worksheet have series of sums from m2120 out 150 columns and I
want to take these sums and transpose them to report2. (so they run
vertically on report2 down column 2)
by using ='report1'm2120, then ...n2120...o2120 and so forth, but i am
getting m2121, m2122, m2123 and so forth when trying to use autofill
any ideas, thx

 

[RagDyeR]

Enter either of these anywhere in report2, and copy down as needed:

=INDEX(report1!$2120:$2120,ROWS($1:13))

=INDEX(report1!M$2120:FF$2120,ROWS($1:1))

The first allows you to add additional columns without revising the formula.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


on report1 worksheet have series of sums from m2120 out 150 columns and I
want to take these sums and transpose them to report2. (so they run
vertically on report2 down column 2)
by using ='report1'm2120, then ...n2120...o2120 and so forth, but i am
getting m2121, m2122, m2123 and so forth when trying to use autofill
any ideas, thx

 

[Gord Dibben]

You can transpose the data using an offset or indirect formula or manually.

I prefer manually.

Select M2120 and across to end of data in row 2120

Edit>Replace

What: =

With: ^^^

Replace all.

Copy the range and paste special>transpose to column B of Report2 sheet.

Reverse the Edit>Replace at both ranges and you're good to go.


Gord Dibben MS Excel MVP