autofill : can it transpose columns & rows?

[opopanax666]

I'm using Excel 2000 at work, and I've got data in colums, which has to
be transposed to rows on an other worksheet, with a "=" function (so no
copy/paste special - transpose). I input the first 3 cells manually,
then select them and try to autofill the rest of the row. No go...
instead of, logically, increasing the column number, Excel increases
the row letter, which has to stay the same, duh...
Does anybody know how to do this?

 

[Graham Whitehead]

Hmm, i see your problem. Now perhaps someone else may have a simpler way of
doing this - but it can be done simple enough with VBA. Lets say we have a
column of data in cells A1 to A10 which need to be copied into cell
C2,D2,E2.....

dim x as integer
dim intValue as integer (change this to the data you are working with)

for x = 0 to 9
intValue = range("A1").offset(x,0).value
range("C2").offset(0,x).value = intValue
next x

Hope it helps


"opopanax666" <[email protected]>
wrote in message
news[email protected]...

 

[Franz Verga]

I'm using Excel 2000 at work, and I've got data in colums, which has
to be transposed to rows on an other worksheet, with a "=" function
(so no copy/paste special - transpose). I input the first 3 cells
manually, then select them and try to autofill the rest of the row.
No go... instead of, logically, increasing the column number, Excel
increases the row letter, which has to stay the same, duh...
Does anybody know how to do this?

You can use this formula in A1 (if the starting cell is not A1, you have to
adapt the formula) of the destination sheet an then copy across rows and
columns:

=OFFSET(Foglio1!$C$12,COLUMN(A1)-1,ROW(A1)-1)

the assumption is that data in columns are on Sheet1 starting from C12.

You have to adapt the assumption to your real data.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[opopanax666]

Cheers, guys, big help!