Do the values in a range of cells include all members of a set?
 

Suppose I'm assigning basebal positions with the set of strings, P, C, 1, 2,
3, SS, LF, CF, RF. After I've entered the 9 assignments, I want an
indication that I have covered all 9 positions. I can have 9 nested if/match
functions, but it seems like there should be a better way.

Do the values in a range of cells include all members of a set?
 

--
HTH,
Bernie
MS Excel MVP


"Dave" wrote in message
...
Suppose I'm assigning basebal positions with the set of strings, P, C, 1, 2,
3, SS, LF, CF, RF. After I've entered the 9 assignments, I want an
indication that I have covered all 9 positions. I can have 9 nested if/match
functions, but it seems like there should be a better way.

Do the values in a range of cells include all members of a set?
 

Dave,

Aaargh - sorry about that empty message.

Use a combination of Data Validation and another cell with a formula.

Let's say that you have your list of positions in a range named "Positions", and your first cell for
entering the positions is cell E2, with the list extending down column E, to say, cell E25.
Initially, all cells are blank.. Select the cells E2:E25, select Data / Validation... select
custom, and use the formula

=AND(NOT(ISERROR(MATCH(E2,Positions,FALSE))),COUNT IF($E2:$E$25,E2)=1)

Also make sure that you check "Ignore Blanks" on the dialog.

Then in another cell, use the formula

=COUNTA(E2:E25) & " positions assigned"

and you are all done.

HTH,
Bernie
MS Excel MVP


"Dave" wrote in message
...
Suppose I'm assigning basebal positions with the set of strings, P, C, 1, 2,
3, SS, LF, CF, RF. After I've entered the 9 assignments, I want an
indication that I have covered all 9 positions. I can have 9 nested if/match
functions, but it seems like there should be a better way.

Do the values in a range of cells include all members of a set
 

Thanks Bernie!

That helps prevent me from entering an invalid position and that is helpful.
But I what I really want to prevent is entering the same position more than
once.

"Bernie Deitrick" wrote:

Dave,

Aaargh - sorry about that empty message.

Use a combination of Data Validation and another cell with a formula.

Let's say that you have your list of positions in a range named "Positions", and your first cell for
entering the positions is cell E2, with the list extending down column E, to say, cell E25.
Initially, all cells are blank.. Select the cells E2:E25, select Data / Validation... select
custom, and use the formula

=AND(NOT(ISERROR(MATCH(E2,Positions,FALSE))),COUNT IF($E2:$E$25,E2)=1)

Also make sure that you check "Ignore Blanks" on the dialog.

Then in another cell, use the formula

=COUNTA(E2:E25) & " positions assigned"

and you are all done.

HTH,
Bernie
MS Excel MVP


"Dave" wrote in message
...
Suppose I'm assigning basebal positions with the set of strings, P, C, 1, 2,
3, SS, LF, CF, RF. After I've entered the 9 assignments, I want an
indication that I have covered all 9 positions. I can have 9 nested if/match
functions, but it seems like there should be a better way.

Do the values in a range of cells include all members of a set
 

The formula will prevent entering the same value twice.... the "COUNTIF($E2:$E$25,E2)=1" part does
that - try it....

HTH,
Bernie
MS Excel MVP


Thanks Bernie!

That helps prevent me from entering an invalid position and that is helpful.
But I what I really want to prevent is entering the same position more than
once.

Do the values in a range of cells include all members of a set
 

Doh! I had been using E1 instead of E2 but pasted your formula...

Building on your example, I used the list instead of custom for validation
and Custom formatting to highlight when I had a duplicate entered.

Thanks again!

"Bernie Deitrick" wrote:

The formula will prevent entering the same value twice.... the "COUNTIF($E2:$E$25,E2)=1" part does
that - try it....

HTH,
Bernie
MS Excel MVP


Thanks Bernie!

That helps prevent me from entering an invalid position and that is helpful.
But I what I really want to prevent is entering the same position more than
once.