a cell can have a 1 and a 15.

Eh, that's a buzz kill!

How would that 1 and 15 in the same cell appear? Is there a space between
them? A comma? A comma and a space?

1 15
1,15
1, 15

Biff

"Lost in Microbiology" wrote
in message ...
Thanks, that will work, but it is a lot of labor to check 30 columns(a
month's schedule) in that fashion. The other wrinkle I have found, are
people
who work multiple locations, i.e. a cell can have a 1 and a 15. Is this
just
not possible?

I tried doing a column with my required numbers and placing a
=countif(c2:c12, 1)0 which returns TRUE/FALSE, and I can just copy and
paste across to get my answer. But when multiple numbers are present it
throws the function off and it only returns a FALSE.


"T. Valko" wrote:

Ooops!

Typo:

Assume the range for the numbers is B2:B18
=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$17,0)),ROW ($1:$17)),ROWS($1:1))

Should be:

=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$18,0)),ROW( $1:$17)),ROWS($1:1))

Biff

"T. Valko" wrote in message
...
Let's see if we understand this.

You should have the numbers 1 through 17 listed in a column. You want
to
check and make sure every one of those numbers is present. List any
that
are missing.

Is that what you want?

Assume the range for the numbers is B2:B18

Try this array** formula:

=SMALL(IF(ISNA(MATCH(ROW($1:$17),B$2:B$17,0)),ROW( $1:$17)),ROWS($1:1))

Copy down until you get #NUM! errors meaning all missing numbers have
been
returned.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Lost in Microbiology"
wrote in message
...
I am making a schedule in Excel and have work areas numbered 1 through
17.

My rows list each employee and the columns list the day of the week, I
want
to check myself that I have all areas covered. Is there a simple
function
that would tell me if I was missing a number in a column (day of the
month)
and which was missing? Here is an example of what I have set-up:
June
1 2 3 4
Name
Jack Handy 1 2 3 3
Hem Roids 3 4 2 1
Buck Shot 5 6 1 3

Work areas: 1=bench a 4=bench d
2=bench b 5=bench e
3=bench c

Thanks