Copy column to rows in another sheet

I have a column of values extracted from a larger array. I would like to automatically copy the _values_ into a row on another sheet, but with the last item in the column into the first column of the row. I have tried various forms of INDEX, INDIRECT, and TRANSPOSE, but have not found an answer. Can't use Macros and VBA because my friend's Excel won't handle them
A sample 2-sheet workbook is attached.
Any suggestions gratefully received.

If you want to avoid volatile functions, you can put this formula in B3 of Sheet2:

=INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)-COLUMNS($B:B)+1)

then copy across as required.

Hope this helps.

Pete

[Solved] Thanks, Pete.