Golf teams of 2 for 20 teams and each week the low handicap from each team will match play each other and higher will match play.
I have the roster with the handicaps on one tab and the matches on another. The team number is a merged two rows on the roster.
I've tried match, vlookup, sumif, etc. so I'm still back at square one.
Last edited by hobbsofmi; 08-20-2014 at 01:22 PM.
Ok, I've figured out how to get the first guy in the right position by using the tab that I use to input scores and do skins with Vlookup. Then I've tried match/index but it only returned numbers and not text.
See the attached revised file.
Help with Names and Matches.xlsx
So, on Sheet 2, you'll manually enter the team numbers (in Cells G1 & I1) and you want the two members from those two teams with the lowest handicaps to be listed in Cells B7 and B8 and the highest two handicaps in Cells B15 & B16? And I assume you want the corresponding handicaps to be populated in Column C?
hoyasaxa215: Yes, I enter the teams in G1 and I1. I have all the other formulas worked out but automatically putting the name in B7, B8, B15 and B16. I've just stripped them out to keep the file simple.
Okay, so I had to make some modifications to get this to work. I had to reorganize the table in Sheet 1 so there were no blank rows breaking up the data. Now the names should autopopulate when you enter the team numbers.
I kept everything visible so you could see how it works, but when you're done with that, you can
1.HIDE (not delete) Column C in Sheet 1, and
2. HIDE (not delete) Column A in Sheet 2, or simply remove the yellow highlighting and change the text color to white so it's not visible when you print it out.
Let me know if you have any questions or if this doesn't work for you.
Yes, that works but I think with index/match it can be done without adding more to a very complex spreadsheet. It does a league for 20 weeks and keeps all the stats, points, strokes, skins, etc. But if I can't get index/match to work than I'll go this route.
Thanks
Hobbesofmi,
Insert this User Defined Function (UDF) code in a standard module:
This function is provided on the OzGrid Website
Function Nth_Occurrence(range_look As Range, find_it As String, occurrence As Long, offset_row As Long, offset_col As Long) Dim lCount As Long Dim rFound As Range Set rFound = range_look.Cells(1, 1) For lCount = 1 To occurrence Set rFound = range_look.Find(find_it, rFound, xlValues, xlWhole) Next lCount Nth_Occurrence = rFound.Offset(offset_row, offset_col) End Function
In order to accomplish this:
1. Hit Alt + F11
2.On the Menu Go To Insert > Module
3. In the Project Explorer you will now see an icon named Module 1. Double Click It.
4. Paste the above code on the white screen
Worksheet Formulas for Sheet2:
Cell Formula
B7 =Nth_Occurrence(Sheet3!B8:B28,Sheet2!G1,1,0,1)
B8 =Nth_Occurrence(Sheet3!B8:B28,Sheet2!I1,1,0,1)
B15 =Nth_Occurrence(Sheet3!B8:B28,Sheet2!G1,2,0,1)
B16 =Nth_Occurrence(Sheet3!B8:B28,Sheet2!I1,2,0,1)
The Answer is clearer on this link: CLICK HERE
Last edited by mrmmickle1; 08-29-2014 at 03:15 PM.
Using Excel 2010, 2013 & 2016 | Windows 7 | 64 Bit
Yes, this works but I have 10 matches for 20 weeks to do. So that 800 places.
If you can identify a set pattern then you can just programmatically insert the formulas. For example the two formulas above are 8 cells apart. Is this how your data is set up for an entire sheet? Or do you have 200 sheets containing these 4 formulas? Either way it is possible to programmatically insert formulas. If you can provide a pattern I can help you accomplish your goal.
I've got some to work but not all of them. So I went back to trying Index/match. I got the first ones just like I did for VLOOKUP but not the second one. The formula analizer says the right answer but I get a #VALUE! error which I can't seem to clear on Sheet 3. If I can get that to work I know I can get the rest.
Help with Names and Matches.xlsx
I found one way to do it with INDEX, SMALL and ROW that was the same formula in my last update: {=INDEX(Sheet3!C9:C56,SMALL(IF(Sheet3!B9:B56=I1,ROW(Sheet3!$B$8:$B$55)-ROW(Sheet3!$B$8)+1),2))}
The {} is the key that tells the formula that it's not a value. You can't type the {} in the formula but you must do a CTRL SHIFT ENTER when in the formula. Saving in once you do it then you can change the team number on the top and it auto puts in both team members in the right spot. You can do it for all 10 matches for 20 weeks ahead of time and as things changes I've changed the area that it's looking and it's done. See the final attached file.
Glad you were able to get it all sorted out! Thanks for sharing the solution.
mrmmickle1: Thanks for you help too. Your solution did also work but it was just not easy for 10 matches for 20 weeks and multiple tabs.
Now that we are at the end of the season I'll have the data to make sure all the formula's are on all tabs and be ready to blank it for next year.
Oh...how do you mark this solved?
Not sure you can mark it solved. Might not be an option or it could be a moderator right.....
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
hobbsofmi 
Register To Reply
Bookmarks