Richard,
A $ before either the letter or the number will keep this part of the
cell reference unchanged when you copy, i.e. it is an *absolute*
reference.

If you want to make a copy of Sheet1!E1:E1000 to Sheet2!A1:A1000, then
your formula in A1:

=Sheet1!E1

should work properly. When you copy to A2, it should change
automatically to =Sheet1!E2. If you copy to B1 instead, it should
change to =Sheet1!F1.

Now, from what you say, you have data in columns but you want to
transfer them to rows, otherwise I don't see how your experimentation
would have proved fruitless. Beware that Excel has 60000+ rows but only
256 columns.

If you want to copy columns to rows you can use:
Select and Edit|Copy
Select first cell of destination and Edit|Paste Special... and click
the Transpose checkbox.

Does this help?
Kostis Vezerides