transpose with formula

[fletch]

Hi,

I have a list of headings across the top row.

How can I use a formula which transposes this down a column.

row > a1 b1 c1 d1 (etc)

column

a1
b1
c1
d1
(etc)

I can't use the paste special transpose as it needs to dynamically change.

Any ideas?

 

[Seti]

I've just started playing around with the ADDRESS function so this may or may not be exactly what you want. If this doesn't do it, I bet the OFFSET function will.

Book2
	A	B	C	D	E
1		100	200	300	400
2	100
3	200
4	300
5	400
Sheet1

 

[fletch]

Thank you,

I'd been playing around for ages before the post - I've got somthing now.

 

[Aladin Akyurek]

Book4
	A	B	C	D	E
1	Jan	Feb	Mar	Apr	May
2
3	Jan	Jan
4	Feb	Feb
5	Mar	Mar
6	Apr	Apr
7	May	May
8
Sheet1

Using INDEX()...

A3, copied down:

=INDEX($A$1:$E$1,ROW()-ROW(A$3)+1)

Using OFFSET()...

B3, copied down:

=OFFSET($A$1,0,ROW()-ROW(B$3),1,1)

Recall that INDEX is non-volatile.