I mis-typed it, i used: =INDEX(Sheet1!$A:$A,COLUMNS($A:$A))
instead of: =INDEX(Sheet1!$A:$A,COLUMNS($A:A))

On a side note, do you know if this introduces memory usage issues over some
other way of doing it in an array function? I'm doing this in several sheets,
and since it spans something like 16K+ columns in each sheet in 2K7, I think
it's causing memory usage issues (~1GB of Memory for one XLTX file).

"Max" wrote:

Are you sure? It works darned well for me in testing here, and it should just
as well for you, too. I'm not sure what could possibly have happened over
there when you tried it. But as it can be easily re-tested, re-read my steps,
then give it another go. Just copy n paste the expression direct from my
posting into Sheet2's A1, then drag A1 to copy ACROSS. Do NOT retype the
expression, you may introduce inadvertent typo(s). For eg note that it is
COLUMNS (with an S). Let me know here your results.
--
Max
Singapore
---
"Rad131304" wrote:
This seems to return the first value in the Index range $A:$A for all cells
where the formula is pasted.

"Max" wrote:

Perhaps something simpler would suffice?
Assume your source data in Sheet1's col A, in A1 down
In Sheet2,
In A1: =INDEX(Sheet1!$A:$A,COLUMNS($A:A))
Copy across to cover the max expected extent of source data. That should
return it transposed as desired. "Excess" fills will populate as zeros, which
you can easily suppress visually in that sheet via ToolsOptionsView tab
(uncheck Zero values). voila? celebrate it, hit the YES below