Transposing without using Transpose formula

I am aware there is a transpose function, however I do not want to use this as you are unable to change an array easily.

What I would like to do is create a formula that selects A2:A5 and transposes this in cells B1:E1 as shown below.

Any suggestions are welcome.

Thanks

A B C D E
1 Alpha Beta Gamma Delta
2 Alpha
3 Beta
4 Gamma
5 Delta

In B1 use the formula

Please Login or Register  to view this content.

and copy over through column F

B1:

=INDEX($A$2:$A$5,COLUMNS($B1:B1))
copy across

(GuruWannaB, if you can, avoid OFFSET given it's Volatile -- not always feasible I know)

(GuruWannaB, if you can, avoid OFFSET given it's Volatile -- not always feasible I know)

INDEX is also volatile in Excel 2003...but not in Excel 2007

Nope - not since '97

http://www.decisionmodels.com/calcsecretsi.htm

There are discrepancies between what MS says is volatile and what in reality is/is not ... Charles Williams' site above is regarded by many as the Bible of XL Calculation.

EDIT: I should add that I've heard conflicting views from people I would consider at the top of the XL Tree as to whether using INDEX in context of INDEX:INDEX is volatile .... general consensus is it is not though it does generate dirty cells which can give the impression of Volatility.
(in any other context 100% non-volatile)

I stand corrected...thanks for the info!

/bow