Why use TRANSPOSE? It's not doing anything useful.

Array entered:

=IF(ROWS(A$1:A1)COUNTA(B$1:IV$1),"",INDEX($1:$1,S MALL(IF(B$1:IV$1<"",COLUMN(B1:IV1)),ROWS(A$1:A1)) ))

--
Biff
Microsoft Excel MVP


"????? (????) ?????" <micky-a*at*tapuz.co.il wrote in message
...
Forgot the "important part":
{=IF(ROW()COUNTA($B$1:$IV$1),"",INDEX($B$1:$IV$1, SMALL(IF(TRANSPOSE($B$1:$IV$1)0,ROW($1:$255),""), ROW())))}
So... not much difference between this one and my first formula...
Micky


"????? (????) ?????" wrote:

...and even shoreter:
{=INDEX($B$1:$IV$1,SMALL(IF(TRANSPOSE($B$1:$IV$1) 0,ROW($1:$255),""),ROW()))}
Micky


"????? (????) ?????" wrote:

A little more compact Array-Formula will be:
{=LOOKUP("zzz",CHOOSE({1,2},"",INDEX($B$1:$IV$1,SM ALL(IF(TRANSPOSE($B$1:$IV$1)0,ROW($1:$255),""),RO W()))))}
*** The formula should be entered with CTRL+SHIFT+ENTER rather than
with
simply ENTER.
The curly brackets {} are not to be typed, manually, those are entered
by
"Excel".
Micky



"MZ" wrote:

A worksheet has data organized in columns that will be used to create
drop-down lists. How can I display in column A the contents in the
column
headers - i.e. row A contents (which are the titles of each list) so
that I
can see the names of all the column headers without having to scroll
across
the screen.
Note that there are empty columns which will be filled with new lists
that
will be added at later times, so the formula should ignore columns
that have
not header (blank A column) and return column headers that contain
text, but
without creating blank cells in column A that correspond to blank
columns

Example
Column A Column C Column F
Row 1: Colors Colors Texture
Row 2: Texture
--
MZ