Say you have data from A1 thru B4 and you want this data transposed from
cells D5 thru G6
on cells D5 type
=OFFSET(INDIRECT(ADDRESS(1,ROW()-4)),COLUMN()-4,0)
and copy it across and down


"markx" wrote in message
...
Thanks Julie,
In fact, I've tested this function just before, but as you pointed it out,
it doesn't work by dragging it.

I remember that some time ago, I've found somewhere on the net two
functions that work as I would like, but concern only transposing rows to
columns.
One involves INDIRECT, ADDRESS and ROW functions, the other goes with
INDEX and ROW function. Unfortunately I can't find them back right now to
quote them directly.

While searching, I've just found something similar on the groups, I post
it here for the illustration purposes, maybe this could help you imagine a
"general" formula covering my problem (unfortunately I'm unable to do it
by myself:-(:

=INDEX(Sheet1!$1:$1,ROW()*2-1) = this one is supposed to skip every
second value, still row to column

or
=INDIRECT("Summary!"&ADDRESS(38;ROW(1:1);4)) = still row to column, a
particular case

or
=INDIRECT("Summary!"&SUBSTITUTE(ADDRESS(1;ROW(1:1) ;4);"1";"")&"38") =
still row to column, same post as before

Thanks once again for your help and FU on this!
Mark

---------------------

"JulieD" wrote in message
...
Hi

not sure if it fits the description of not too complex and doesn't work
by dragging, but there is a transpose function
say you have
........A..........B
1.....Cat........10
2.....Dog.......15
3....Rat..........20

and you want to transpose this table to the range A5:C6
select A5:C6
type
=TRANSPOSE(A1:B3)
and then press Control & Shift & Enter - not just Enter and the data will
be transposed.

Cheers
JulieD

"markx" wrote in message
...
Hello everybody,

I'm looking for a formula (not VBA nor "Paste Special/Transpose") that
would
transpose columns to rows (or vice versa) by simple dragging of the
cell.
Are you aware of any, hopefully not too complex?

Thanks a lot for your help on this,
Mark