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.