Display index of column headers in column A


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
 
Ad

Advertisements

G

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
 
Ad

Advertisements

L

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

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))))
 
Ad

Advertisements

L

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top