Here's a workaround for you.
1. Until you figure out if this works for you, please insert a new tab
in your workbook, and enter the formula =sheet2!a5, then fill-handle
that down through a8.

2. Highlight those formulas and do a search-and-replace: find the =
sign and replace with the string xyz. This converts the dynamic
formulas to static text strings.

3. Copy those text strings (that used to be formulas), select a new
location for them, and Paste Special: check the Transpose box, and
press OK.

4. Highlight that range, then search and replace: find xyz and replace
with the = sign, which converts the static text strings back to
formulas.

Will this work for you?