Display index of column headers in column A

  • Thread starter Thread starter MZ
  • Start date Start date
M

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
 
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
 
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
 
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))))
 
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
 
Back
Top