| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display index of column headers in column A
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display index of column headers in column A
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. -- Gary''s Student - gsnu200909 "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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display index of column headers in column A
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display index of column headers in column A
....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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display index of column headers in column A
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display index of column headers in column A
Very nice. This will go in my "tool box".
-- Gary''s Student - gsnu200909 "מיכאל (מיקי) אבידן" wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display index of column headers in column A
Be my guest... ;-)
Micky "Gary''s Student" wrote: Very nice. This will go in my "tool box". -- Gary''s Student - gsnu200909 "מיכאל (מיקי) אבידן" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display index of column headers in column A
Slightly shorter:
=IF(ROW()COUNTA($B$1:$IV$1),"",INDEX($B$1:$IV$1,S MALL(IF($B$1:$IV$10,COLUMN($A:$IU)),ROW()))) Eliminated need for TRANSPOSE by changing ROW to COLUMN. Eliminated the second "" as SMALL will ignore FALSE. Lars-ke On Sun, 17 Jan 2010 09:02:01 -0800, ????? (????) ????? <micky-a*at*tapuz.co.il wrote: 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display index of column headers in column A
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display index of column headers in column A
Even shorter:
=IF(ROW()COUNTA(B$1:IV$1),"",INDEX(B$1:IV$1,SMALL (IF(B$1:IV$10,COLUMN(A:IU)),ROW()))) Eliminated some $ where they are not needed. Lars-ke On Sun, 17 Jan 2010 19:00:54 +0100, Lars-ke Aspelin wrote: Slightly shorter: =IF(ROW()COUNTA($B$1:$IV$1),"",INDEX($B$1:$IV$1, SMALL(IF($B$1:$IV$10,COLUMN($A:$IU)),ROW()))) Eliminated need for TRANSPOSE by changing ROW to COLUMN. Eliminated the second "" as SMALL will ignore FALSE. Lars-ke On Sun, 17 Jan 2010 09:02:01 -0800, ????? (????) ????? <micky-a*at*tapuz.co.il wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lookup using column headers and row headers | Excel Discussion (Misc queries) | |||
| Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
| Using an Index by Column Headings instead of Column Numbers | New Users to Excel | |||
| make column values column headers | Excel Discussion (Misc queries) | |||
| Excel - returning column headers in a seperate column | Excel Discussion (Misc queries) | |||
Linear Mode
