Hey all
I need some help in creating a conditional formatting
Cell C3:S8 is where numbers will be added
Cell A10:AC35 have names and numbers, If any of the number match in Cell C3:S8 I would like those numbers highlighted in yellow.
If one person or persons are all cells are highlighted I would the player or player name listed in cells V3
I have uploaded an example
Thanks ahead!
Last edited by Killer17; 11-02-2008 at 09:32 PM.
for the first part
select c10:h35
use conditional format f0rmula is
=COUNTIF($C$3:$S$8,C10)
choose yellow
I used conditional formatting as suggested by Martin....then I added a helper column to total the correct numbers for each row, i.e.. in T10 copied down
=SUMPRODUCT((COUNTIF($C$3:$S$8,C10:H10)>0)+0)
Then in V3
=IF(ROWS(V$3:V3)>COUNTIF(T$10:T$35,6),"",INDEX(A$10:A$35,SMALL(IF(T$10:T$35=6,ROW(T$10:T$35)-ROW(T$10)+1),ROWS(V$3:V3))))
confirmed with CTRL+SHIFT+ENTER and copied down
You can hide coumn T if you wish.....
see attached
See attached...
I added a helper column in column AD to count the matches... and used that for the formula in V3 down to extract your players...
Note: Formula in V3 is CTRL+SHIFT+ENTER entered... and then copied down.
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.
Awesome guys thanks for the help....
another way ,in the unlikley event of a tie both are given and if more than 2 a warning is given to check
Hello Martin,
I think that only works in your example, though, when Player 1 is a winner.
If the first winner was, say, Player 4 then those formulas aren't going to work, both V3 and V4 will say "Player 4"
blow and bother i forgot to check that!
just did grrr should have used row() somewhere
or
=INDEX(INDIRECT("a"&MATCH(6,I10:$I$35,0)+10&":$I$35"),MATCH(6,INDIRECT("I"&MATCH(6,I10:$I$35,0)+10&":$I$35"),0),1) i like your solution better!
i was going to do an index /small array on 6 but lost the will! oh just for the excercise then
in v3 array is
Please Login or Register to view this content.
Last edited by martindwilson; 11-02-2008 at 11:04 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks