You might be able to do it with another formula instead.

e.g

something like:

=INDEX('Sheet1'!$B:$B,COLUMNS($A$1:A$1))

When you enter this formula in the new sheet, it looks at column B of the Sheet1 and extracts the item at the top of the range (i.e. B1).. as you copy it across the cells horizontally, it extracts the next vertically sequential cell value in Sheet1...

Does that work for you?

Of course, adjust sheet name and range to suit.