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

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!
================================================== ===


"Reno" wrote in message
...
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

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

EditReplace

What: =

With: ^^^

Replace all.

Copy the range and paste specialtranspose to column B of Report2 sheet.

Reverse the EditReplace at both ranges and you're good to go.


Gord Dibben MS Excel MVP



On Thu, 7 May 2009 07:46:01 -0700, Reno
wrote:

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