I have a range of 5 cells. These cells can contain a blank, or a number (the
numbers are 1,2,3,4 and 5). Numbers cannot repeat within the range (i.e. if
there is a 1 in a cell, the 1 cannot occur in any of the other 4 cells).

With the cursor in the first cell, how can I have Excel determine whether
there is a 1 in any of the other 4 cells; then determine whether there is a 2
in any of the other 4 cells; then determine whether there is a 3, etc? There
has to be some way, other than trying to write a super-complex IF statement.
Do you have any ideas?

--
John

I have a range of 5 cells. These cells can contain a blank, or a number
(the
numbers are 1,2,3,4 and 5). Numbers cannot repeat within the range (i.e.
if
there is a 1 in a cell, the 1 cannot occur in any of the other 4 cells).

With the cursor in the first cell, how can I have Excel determine whether
there is a 1 in any of the other 4 cells; then determine whether there is
a 2
in any of the other 4 cells; then determine whether there is a 3, etc?
There
has to be some way, other than trying to write a super-complex IF
statement.
Do you have any ideas?

If you are simply looking to see if all the cells have been filled in (here,
I am assuming you have procedures in place to make sure they are properly
filled in), SUM the range and see if they total 15.

Rick

actually, I want to determine what digit to place in a cell, based on which
digits already have been used in the other 4 cells. In other words, if my 5
cells contain: blank,2,4,5,1 - I want to have Excel tell me that the first
cell should be a 3, since the numbers 1,2,4,5 have been used.

--
John


"Rick Rothstein (MVP - VB)" wrote:

I have a range of 5 cells. These cells can contain a blank, or a number
(the
numbers are 1,2,3,4 and 5). Numbers cannot repeat within the range (i.e.
if
there is a 1 in a cell, the 1 cannot occur in any of the other 4 cells).

With the cursor in the first cell, how can I have Excel determine whether
there is a 1 in any of the other 4 cells; then determine whether there is
a 2
in any of the other 4 cells; then determine whether there is a 3, etc?
There
has to be some way, other than trying to write a super-complex IF
statement.
Do you have any ideas?

If you are simply looking to see if all the cells have been filled in (here,
I am assuming you have procedures in place to make sure they are properly
filled in), SUM the range and see if they total 15.

Rick

=15-SUM(A2:A5)
__________________________________________________ _______________________

"John" wrote in message
...
actually, I want to determine what digit to place in a cell, based on
which
digits already have been used in the other 4 cells. In other words, if my
5
cells contain: blank,2,4,5,1 - I want to have Excel tell me that the first
cell should be a 3, since the numbers 1,2,4,5 have been used.

--
John


"Rick Rothstein (MVP - VB)" wrote:

I have a range of 5 cells. These cells can contain a blank, or a number
(the
numbers are 1,2,3,4 and 5). Numbers cannot repeat within the range
(i.e.
if
there is a 1 in a cell, the 1 cannot occur in any of the other 4
cells).

With the cursor in the first cell, how can I have Excel determine
whether
there is a 1 in any of the other 4 cells; then determine whether there
is
a 2
in any of the other 4 cells; then determine whether there is a 3, etc?
There
has to be some way, other than trying to write a super-complex IF
statement.
Do you have any ideas?

If you are simply looking to see if all the cells have been filled in
(here,
I am assuming you have procedures in place to make sure they are properly
filled in), SUM the range and see if they total 15.

Rick