Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
Macro to hide blank cells in a range | Excel Discussion (Misc queries) | |||
Find dates in a range; then sum values in that range by a criteria | Excel Discussion (Misc queries) | |||
Display first, second, etc Nonblank Cells in a Range | Excel Worksheet Functions | |||
Count occurances in range of cells | Excel Worksheet Functions |