Use conditional formatted cell and copy a row entry and col entry based on result

[FDG]

I have a spreadsheet that has golf scores entered and using conditional formatting I have isolated a "skin" by a player having the lowest unmatched score on each hole. Right now I manually copy the players name from the row and hole number from the col of the highlighted cell.
I am in excel 2010 and the formula in conditional formatting function I created is:
Applies to:

=$AF$7:$AF$38,$BD$7:$BD$38,$BA$7:$BA$38,$AX$7:$AX$38,$AU$7:$AU$38,$AR$7:$AR$38,$AO$7:$AO$38,$AL$7:$AL$38,$AI$7:$AI$38,$AB$7:$AB$38,$Y$7:$Y$38,$V$7:$V$38,$S$7:$S$38,$P$7:$P$38,$M$7:$M$38,$J$7:$J$38,$G$7:$G$38,$D$7:$D$38

Formula:
=AND(D7=MIN(D$7:D$38),COUNTIF(D$7:D$38,MIN(D$7:D$38))=1)
this works very well. But I am locked up on how to code a separate sheet to get the name and hole number of a winner.

I would appreciate any comments for a solution.
Thanks

[Evolta]

Could you post an example file without any sensitive information?
Is much easier to clearly see the problem and to provide a nice solution.

[FDG]

I am attaching a workbook. The work book names are fairly self explanatory except for Sheet 1. All the sheets except Input are protected as locked. All data is entered via the input sheet to setup the golf flights A, B, C, D.
Scores are entered in each Flight sheet hole by hole. There are adjustments made on the sheets below the entry rows. Here is my quest. I currently take the nicely highlighted "Skin" and manually enter on the Input sheet: Player Name, hole (s) and Prize.
I want to code Sheet 1 in the Skins area to copy the Name and Hole(s) where a skin was won. I can do the Prize from the input sheet as it is a function of several factors. I hope this will help clear up my intention.Golf test2.xlsm

[Evolta]

I've looked it over.

Don't know if its possible to do it an easy way.. my excel knowledge is too limited for that.
I would be able (will take me a long time) to make a macro that eventually can get it done.
(can't promise when I'm done, since I have other things to do today)

But hopefully some experts can jump in here, so they can help you.

[FDG]

Thank you for your consideration. I have the feeling that it could be done with lookup, but I am not sure how to set the initial condition since conditional formatting is an intrinsic function. I had tried using the formulas I have for pulling the scores over to sheet1, but stumped on how to get the conditional formula into the if statement.
I really appreciate you taking the time to look it over.

[Evolta]

Yes I suppose it can be done with an array formula, but it also gets tricky since you can "win?" on multiple holes.

[Philb1]

What is it you're trying to do?
I know diddly about golf so you're going to have to explain better lol
What's a skin?

[Evolta]

Well hopefully this comes anything near what you want.
Just use the button on input page, it will retrieve the information from sheets 1 till 4 (the A till D sheets).
I changed on the input sheet the $$$$$ signs for the values per hole. (needed it to calculate the other results)

Golf test2_2.xlsm

[FDG]

Wow, That is so very close. The $$$$ symbols were header above The winnings. That col will have to be entered manually as the amount won on each "skin" varies with the number of players. That formula is number of players in flight -1 player times $5. ($5 is retained in a Kitty for prizes at the end of the year.) In addition, The payment amount must be a whole number and equal for each winner, remainder is added to prize money. Example. 4 winners with 19 players in flight would be (18*5)/4, $22 for each skin and $2 added to the $5 held in Kitty, total to Kitty: $7. The skins sheet would only show $22 for each skin. If a player had 2 skins it would by $44 for that player and $22 for each of the others.
A Skin is when all the players in one Flight (handicap group) (0 - 15) in A Flight-- have played a the same hole and one player had the lowest untied score, they win a skin. In this case money from the pool entry fee of $5 per player, Less $5 for Kitty (Prize pool) and leftovers to make equal payment in whole dollars.
Hope that helps.
Now for the closeness. How can I make the corrections to place the winnings on the winner name line and not on the header line. The math formula for determination of prize amounts is optional and could be done manually. Other than that, I want to transport the macro to a new sheet every week which will have different numbers of players in each flight, sometime more sometimes less. So how do I do this.
I cannot thank you enough for this outstanding work. With no knowledge of the sport, you have created a really nice macro. If you can find time to explore the small corrections mentioned I will to mark this solved.

[Evolta]

I'll try to add some changes. Was too blinded by the example that I did some dumb things :D
I actually didn't make it possible to run this formula if names are filled in -.- Very sorry about that.

I get the payment, but in A flight: Name 11 probably isnt counted. (same as 5 and 13 in flight B..... 13 and 20 in flight C)
They all have 99 in column B. Is below how it can be counted?

Please Login or Register  to view this content.

My assumption that "not in skin" and "new/guests" do not play for skins, is that correct?

I will really try to fix these things.



-edit
Prices are now based on number of players
Names can now be filled in
Golf test2_3.xlsm

If there are any other questions just ask. And next time I'm in the states I expect to be invited for a free game of golf (including a lesson)

[FDG]

Your assumptions are mostly correct. The 99 players may or may not be counted. Some are "no show", players that signed up to play, but at the last minute could not play. Others are counted when they begin play but do not finish. DNF players are still eligible for winning on the holes played. I will see if I can set a value to identify a no show that is different than a DNF. You absolutely are invited to play when you visit. I will try your newer version sometime today. Just looked at your formula. It seems that it would work. Will make sure to have 99 reserved for NS and use a different value for DNF.

[FDG]

To the forum, After some years I have found a problem in the formula that handles the skins payouts in :Golf test2_3.xlsm in the 05-21-2015 post from Evolta. Have not been able to track him down, but the problem seems like it should be an easy fix.
I have exhausted my knowledge so am asking for help. The problem: in the golf scores on a sheet if a winning low score is a single digit number and another player has a double digit score that ends in the same number, the player is not counted as winning.
Example is a player scores a net 2 on a hole that is the low single score and another player scores a 12 on that hole is nullifies the win for copy to tab "input" even though the low score on the hole is highlighted as winner. So the test of standalone low score in the payout part of the macro "update skins" is not considering double digit scores. It only is testing the score in the right hand place. so 12 is considered 2 and the player is not passed to the input sheet.
So how can I change to make double digits not count?