array transpose for dynamic data without macros

[Rad131304]

I'm having a problem with array functions and transpose.

I currently have a column of data that is unknown in length (it will be
different every time it is used), and I would like to auto-populate column
headers in a different sheet with this data. ATM I am using the following:

=TRANSPOSE(OFFSET(<first_column_element>,0,0,COUNTA(<column>)-1,1))

My problem is that, since I have no idea how many items there will be, I
have to apply this array formula to the entire span of columns which makes
for a lot of #N/A columns. I've *fixed* this by conditionally formatting the
font color to be the same as the cell background so the #N/A is not visible.

I'd really like to make this array have a dynamic size based upon COUNTA()
so I don't have to use my font color workaround, but I have no idea how to do
this (or if it is even possible) without macros. I need to avoid macros if
possible since I don't know if my users have the ability to allow macros to
run.

 

[zvkmpw]

Here's one way, but it's different from the approaches you mentioned.

In the left-most column header, put
=IF(OFFSET(Sheet1!$A$1,COLUMN()-2,0)="","",
OFFSET(Sheet1!$A$1,COLUMN()-2,0))
but don't use 2; instead, use whatever number makes the OFFSET point
to <first_column_element>. If <first_column_element> isn't in Sheet1
column A, change those parts too.

Then select the cell containing the formula and drag rightward for the
maximum length the column can ever have. Because of the IF test, a
header cell should be simply empty if the corresponding column element
is absent.

 

[Max]

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 Tools>Options>View tab
(uncheck Zero values). voila? celebrate it, hit the YES below

 

[Rad131304]

Thanks, this worked!

 

[Rad131304]

This seems to return the first value in the Index range $A:$A for all cells
where the formula is pasted.

 

[Max]

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.

 

[Rad131304]

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]

There you go. Glad you got it working
Start a new thread for your new query.
I don't use xl07, but you can always use manual calc mode to control calcs
only when needed. That simple way should work in any version.