Display index of column headers in column A

[MZ]

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

 

[Gary''s Student]

If you have sparse data in B1 thru IV1 (data mixed with empties) and want to
list the data in column A, then in A1 enter the array formula:

=IF(ROWS($1:1)<=COUNTA($B$1:$IV$1),INDEX($B$1:$IV$1,SMALL(IF($B$1:$IV$1<>"",COLUMN($B$1:$IV$1)-MIN(COLUMN($B$1:$IV$1))+1),ROWS($1:1))),"")

and copy down.

This is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.

 

[מיכ×ל (מיקי) ×בידן]

A little more compact Array-Formula will be:
{=LOOKUP("zzz",CHOOSE({1,2},"",INDEX($B$1:$IV$1,SMALL(IF(TRANSPOSE($B$1:$IV$1)>0,ROW($1:$255),""),ROW()))))}
*** 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

 

[מיכ×ל (מיקי) ×בידן]

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

 

[מיכ×ל (מיקי) ×בידן]

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

 

[Gary''s Student]

Very nice. This will go in my "tool box".

 

[מיכ×ל (מיקי) ×בידן]

Be my guest... ;-)
Micky

Very nice. This will go in my "tool box".

 

[Lars-Åke Aspelin]

Slightly shorter:

=IF(ROW()>COUNTA($B$1:$IV$1),"",INDEX($B$1:$IV$1,SMALL(IF($B$1:$IV$1>0,COLUMN($A:$IU)),ROW())))

Eliminated need for TRANSPOSE by changing ROW to COLUMN.
Eliminated the second "" as SMALL will ignore FALSE.

Lars-Åke

 

[T. Valko]

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

Array entered:

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

 

[Lars-Åke Aspelin]

Even shorter:

=IF(ROW()>COUNTA(B$1:IV$1),"",INDEX(B$1:IV$1,SMALL(IF(B$1:IV$1>0,COLUMN(A:IU)),ROW())))

Eliminated some $ where they are not needed.

Lars-Åke