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.

--
HTH,
Bernie
MS Excel MVP


"Dave" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>

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))),COUNTIF($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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>

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))),COUNTIF($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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> >
>
>
>

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.

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.
>
>
>