I work with Boy Scouts. They have lots of requirements to earn badges. I
have a spreadsheet which lists a summary of requirements in the first column
and the boys names in the first row (column headers). When a boy completes
the requirement, I put a "1" in the cell where the name/reqrmnt intersect. I
need to create a separate report that lists the full requirement and lists
names of boys who have NOT completed requirements. So, I need a formula to
search the summary spreadsheet and retrieves the boys names (column header)
who have not completed a requirement. Ideally, all names would be returned
to a single cell however, they could be returned to a row of cells
Any help or pointing me in the right direction would be sincerely appreciated.

Try this...

Names in the range B1:F1
Requirements in the range A2:A5

For your summary table the requirements are listed in the range A10:A13

Enter this array formula** in B10:

=IF(COLUMNS($B10:B10)<=COUNTIF($B2:$F2,""),INDEX($ B$1:$F$1,SMALL(IF($B2:$F2="",COLUMN($B1:$F1)),COLU MNS($B10:B10))-MIN(COLUMN($B1:$F1))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down to B13 then across until you get a full column of blanks.

--
Biff
Microsoft Excel MVP


"c_diver" wrote in message
...
I work with Boy Scouts. They have lots of requirements to earn badges. I
have a spreadsheet which lists a summary of requirements in the first
column
and the boy's names in the first row (column headers). When a boy
completes
the requirement, I put a "1" in the cell where the name/reqrmnt intersect.
I
need to create a separate report that lists the full requirement and lists
names of boys who have NOT completed requirements. So, I need a formula
to
search the summary spreadsheet and retrieves the boys names (column
header)
who have not completed a requirement. Ideally, all names would be
returned
to a single cell however, they could be returned to a row of cells
Any help or pointing me in the right direction would be sincerely
appreciated.

Biff - Brilliant! I tried to follow the logic - what a mind twister! But,
it works great! Thank you!

If it's not too much trouble, there is a minor thing that would sure make it
easier to maintain. Most months, we have new kids coming in and some kids
graduating to the next age group, I wanted to have some blank column headers
(no boys names) so I could add kids as they move in without modifying the
equation throughout the spreadsheet every time. I tried extending the name
range in your formula to include several blank columns beyond the last boy.
For example, the name rage you used was B1:F1, so I changed this to B1:I1 but
columns G,H,I did not have boys names entered. The result returns zeros to
the cells where columns without boys names were evaluated . Would there be a
simple way to not have the zeros show up?

Thank you again!

"T. Valko" wrote:

Try this...

Names in the range B1:F1
Requirements in the range A2:A5

For your summary table the requirements are listed in the range A10:A13

Enter this array formula** in B10:

=IF(COLUMNS($B10:B10)<=COUNTIF($B2:$F2,""),INDEX($ B$1:$F$1,SMALL(IF($B2:$F2="",COLUMN($B1:$F1)),COLU MNS($B10:B10))-MIN(COLUMN($B1:$F1))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down to B13 then across until you get a full column of blanks.

--
Biff
Microsoft Excel MVP


"c_diver" wrote in message
...
I work with Boy Scouts. They have lots of requirements to earn badges. I
have a spreadsheet which lists a summary of requirements in the first
column
and the boy's names in the first row (column headers). When a boy
completes
the requirement, I put a "1" in the cell where the name/reqrmnt intersect.
I
need to create a separate report that lists the full requirement and lists
names of boys who have NOT completed requirements. So, I need a formula
to
search the summary spreadsheet and retrieves the boys names (column
header)
who have not completed a requirement. Ideally, all names would be
returned
to a single cell however, they could be returned to a row of cells
Any help or pointing me in the right direction would be sincerely
appreciated.

The easiest way to fix that is to wrap the INDEX function inside the T
function:

=IF(COLUMNS($B10:B10)<=COUNTIF($B2:$I2,""),T(INDEX ($B$1:$I$1,SMALL(IF($B2:$I2="",COLUMN($B1:$I1)),CO LUMNS($B10:B10))-MIN(COLUMN($B1:$I1))+1)),"")

--
Biff
Microsoft Excel MVP


"c_diver" wrote in message
...
Biff - Brilliant! I tried to follow the logic - what a mind twister!
But,
it works great! Thank you!

If it's not too much trouble, there is a minor thing that would sure make
it
easier to maintain. Most months, we have new kids coming in and some kids
graduating to the next age group, I wanted to have some blank column
headers
(no boys names) so I could add kids as they move in without modifying the
equation throughout the spreadsheet every time. I tried extending the
name
range in your formula to include several blank columns beyond the last
boy.
For example, the name rage you used was B1:F1, so I changed this to B1:I1
but
columns G,H,I did not have boys names entered. The result returns zeros to
the cells where columns without boys names were evaluated . Would there
be a
simple way to not have the zeros show up?

Thank you again!

"T. Valko" wrote:

Try this...

Names in the range B1:F1
Requirements in the range A2:A5

For your summary table the requirements are listed in the range A10:A13

Enter this array formula** in B10:

=IF(COLUMNS($B10:B10)<=COUNTIF($B2:$F2,""),INDEX($ B$1:$F$1,SMALL(IF($B2:$F2="",COLUMN($B1:$F1)),COLU MNS($B10:B10))-MIN(COLUMN($B1:$F1))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Copy down to B13 then across until you get a full column of blanks.

--
Biff
Microsoft Excel MVP


"c_diver" wrote in message
...
I work with Boy Scouts. They have lots of requirements to earn badges.
I
have a spreadsheet which lists a summary of requirements in the first
column
and the boy's names in the first row (column headers). When a boy
completes
the requirement, I put a "1" in the cell where the name/reqrmnt
intersect.
I
need to create a separate report that lists the full requirement and
lists
names of boys who have NOT completed requirements. So, I need a
formula
to
search the summary spreadsheet and retrieves the boys names (column
header)
who have not completed a requirement. Ideally, all names would be
returned
to a single cell however, they could be returned to a row of cells
Any help or pointing me in the right direction would be sincerely
appreciated.

Everything works! You are the Excel master!

Thank you very much for taking your time to help a total stranger out on this.

Jeff

"T. Valko" wrote:

The easiest way to fix that is to wrap the INDEX function inside the T
function:

=IF(COLUMNS($B10:B10)<=COUNTIF($B2:$I2,""),T(INDEX ($B$1:$I$1,SMALL(IF($B2:$I2="",COLUMN($B1:$I1)),CO LUMNS($B10:B10))-MIN(COLUMN($B1:$I1))+1)),"")

--
Biff
Microsoft Excel MVP


"c_diver" wrote in message
...
Biff - Brilliant! I tried to follow the logic - what a mind twister!
But,
it works great! Thank you!

If it's not too much trouble, there is a minor thing that would sure make
it
easier to maintain. Most months, we have new kids coming in and some kids
graduating to the next age group, I wanted to have some blank column
headers
(no boys names) so I could add kids as they move in without modifying the
equation throughout the spreadsheet every time. I tried extending the
name
range in your formula to include several blank columns beyond the last
boy.
For example, the name rage you used was B1:F1, so I changed this to B1:I1
but
columns G,H,I did not have boys names entered. The result returns zeros to
the cells where columns without boys names were evaluated . Would there
be a
simple way to not have the zeros show up?

Thank you again!

"T. Valko" wrote:

Try this...

Names in the range B1:F1
Requirements in the range A2:A5

For your summary table the requirements are listed in the range A10:A13

Enter this array formula** in B10:

=IF(COLUMNS($B10:B10)<=COUNTIF($B2:$F2,""),INDEX($ B$1:$F$1,SMALL(IF($B2:$F2="",COLUMN($B1:$F1)),COLU MNS($B10:B10))-MIN(COLUMN($B1:$F1))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Copy down to B13 then across until you get a full column of blanks.

--
Biff
Microsoft Excel MVP


"c_diver" wrote in message
...
I work with Boy Scouts. They have lots of requirements to earn badges.
I
have a spreadsheet which lists a summary of requirements in the first
column
and the boy's names in the first row (column headers). When a boy
completes
the requirement, I put a "1" in the cell where the name/reqrmnt
intersect.
I
need to create a separate report that lists the full requirement and
lists
names of boys who have NOT completed requirements. So, I need a
formula
to
search the summary spreadsheet and retrieves the boys names (column
header)
who have not completed a requirement. Ideally, all names would be
returned
to a single cell however, they could be returned to a row of cells
Any help or pointing me in the right direction would be sincerely
appreciated.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"c_diver" wrote in message
...
Everything works! You are the Excel master!

Thank you very much for taking your time to help a total stranger out on
this.

Jeff

"T. Valko" wrote:

The easiest way to fix that is to wrap the INDEX function inside the T
function:

=IF(COLUMNS($B10:B10)<=COUNTIF($B2:$I2,""),T(INDEX ($B$1:$I$1,SMALL(IF($B2:$I2="",COLUMN($B1:$I1)),CO LUMNS($B10:B10))-MIN(COLUMN($B1:$I1))+1)),"")

--
Biff
Microsoft Excel MVP


"c_diver" wrote in message
...
Biff - Brilliant! I tried to follow the logic - what a mind twister!
But,
it works great! Thank you!

If it's not too much trouble, there is a minor thing that would sure
make
it
easier to maintain. Most months, we have new kids coming in and some
kids
graduating to the next age group, I wanted to have some blank column
headers
(no boys names) so I could add kids as they move in without modifying
the
equation throughout the spreadsheet every time. I tried extending the
name
range in your formula to include several blank columns beyond the last
boy.
For example, the name rage you used was B1:F1, so I changed this to
B1:I1
but
columns G,H,I did not have boys names entered. The result returns zeros
to
the cells where columns without boys names were evaluated . Would
there
be a
simple way to not have the zeros show up?

Thank you again!

"T. Valko" wrote:

Try this...

Names in the range B1:F1
Requirements in the range A2:A5

For your summary table the requirements are listed in the range
A10:A13

Enter this array formula** in B10:

=IF(COLUMNS($B10:B10)<=COUNTIF($B2:$F2,""),INDEX($ B$1:$F$1,SMALL(IF($B2:$F2="",COLUMN($B1:$F1)),COLU MNS($B10:B10))-MIN(COLUMN($B1:$F1))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Copy down to B13 then across until you get a full column of blanks.

--
Biff
Microsoft Excel MVP


"c_diver" wrote in message
...
I work with Boy Scouts. They have lots of requirements to earn
badges.
I
have a spreadsheet which lists a summary of requirements in the
first
column
and the boy's names in the first row (column headers). When a boy
completes
the requirement, I put a "1" in the cell where the name/reqrmnt
intersect.
I
need to create a separate report that lists the full requirement and
lists
names of boys who have NOT completed requirements. So, I need a
formula
to
search the summary spreadsheet and retrieves the boys names (column
header)
who have not completed a requirement. Ideally, all names would be
returned
to a single cell however, they could be returned to a row of cells
Any help or pointing me in the right direction would be sincerely
appreciated.