Column with values starting in A2 going down

=OFFSET($A$2,COLUMN(A:A)-1,)

copy across and it will return A2, A3, A4 etc

Row with values starting in D2

=OFFSET($D$2,,ROW(1:1)-1,)

copy down and it will return D2, E2, F2 etc

--

Regards,

Peo Sjoblom


"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