I'm attempting to use the countif formula to make sure a number is entered only once in 4 nonconnected ranges, I492:I496, I499:I503, K492:K496 and K499:K503. I've tried everything I know and as I'm self taught and my teacher is an idiot, I'm not having any luck. Any help will be very much appreciated as I'm down to the last 2 hairs on my head and one of them is already loose.
Why not just add the 4 Countif functions together:
=Countif(I492:I496,X1)+Countif(I499:I503,X1)+Countif(K492:K496,X1)+Countif(K499:K503,X1)
Where X1 houses value/string to count.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
I tried your solution, but it didn't work. At least it didn't say that the formula was bad, it just didn't work. Thanks anyway
What didn't work about it? Did it give an error? or Wrong count?Originally Posted by village_idiot
Remember to change X1 to the actual cell housing the value to count or replace it with the value you wnat to count.
Last edited by NBVC; 05-14-2007 at 04:02 PM.
It didn't cause an error, but then again it didn't do anything at all. I know that in the bigger picture this problem isn't much, but like I said I only have 2 hairs left and one is loose. I'll included a zipped file so you can see what I'm doing. I want to keep the people from drawing a card that is already drawn. Again thanks for any and all help.
So have your ranges changed to I5:I9, K5:K9, I12:I16, K12:K16?
I don't see where you tried the formula?
Maybe...
=SUMPRODUCT(COUNTIF(INDIRECT({"I492:I496","I499:I503","K492:K496","K499:K503"}),X1))
Hope it helps!
To NBVC: the addresses has changed, because the work sheet I was working from has a lot of other info that I garnered from this fantastic web site and it's even more fantastic people who take the time to help me and my fellow noobs.
To VANE0326: thanks for the info, but according to Excel and I quote: "You may not use unions, intersections, or arrey constants for Data Validation or Conditional Formats" unquote. So, I'm back at the same point, not that it matters much, my last 2 hairs fell out last night, so I guess I'll have to try it from another direction.
Thanks for all the info and I'll keep looking.
To NBVC: the addresses has changed, because the work sheet I was working from has a lot of other info that I garnered from this fantastic web site and it's even more fantastic people who take the time to help me and my fellow noobs.
If you specify exactly what you are doing and what results you expect and where you expect them, referencing the cells in the sheet you attached, I am sure we can come up with a solution....
What I want to do is to let the 4 player H5:H9, H12:H16, J5:J9 and J12:J16 draw new cards using I5:I9, I12:I16, K5:K9 and K12:K16 without any two people picking the same card. It's not real earth shattering I know, but I got it in my head and it lonely in there for it so I have to get the answer so it can leave.
Maybe you want data Validation from the Data menu.
This will pop up a message if someone tries entering a card that has already been selected in any of the 4 hands.
See attached try entering the same number in any 2 boxes.
Is this what you desire?
I tried like you said and nothing happened, the only difference I could see is that there was a conditional format, that also didn't work. What I want to figure out is the proper way to use the data validation on the 4 different ranges, if they aren't connected.
I must have not saved it after I did the DV stuff...
Try again with the attached.
Bingo, just what the doctor ordered. Thanks much and now after work I'll have to dissect your work to see were I messed up. Again thanks to everyone for your help.
Ok. Finally. Great....and you are welcome!
Just one more question, for my own information. Can I do the same thing using range names?
Interestingly enough, it doesn't seem to work when I name the 4 ranges....even thought the outcome is False and should pop up the error. I tried several ways and can't get it.
Hmmmm
Note though: You can use 1 range (H5:I16) since the numerical entries will never equal the card names.
so in I5, you can change the Data Validation formula to =COUNTIF($H$5:$K$16,I5)=1 and then Copy|Paste Special|Validation onto the other cells where one can enter numbers.... again using a named range doesn't seem to take the same way
Last edited by NBVC; 05-15-2007 at 02:05 PM.
I tried naming all 4 ranges one name and didn't get any where with it, but then something else might have been wrong. It really doesn't matter if it works or not as the work sheet isn't very important, I was just trying something new. Again thanks for the help and info, maybe next time I will be able to figure it out myself.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks