autofill : can it transpose columns & rows?

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?

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" <opopanax666.2adhyz_1151936102.1827@excelforum-nospam.com>
wrote in message
news:opopanax666.2adhyz_1151936102.1827@excelforum-nospam.com...
>
> 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?
>
>
> --
> opopanax666
> ------------------------------------------------------------------------
> opopanax666's Profile:
> http://www.excelforum.com/member.php...o&userid=35998
> View this thread: http://www.excelforum.com/showthread...hreadid=557843
>

opopanax666 wrote:
> 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

Cheers, guys, big help!