Leauge Stats - Ranking using LOOKUP/SEARCH/INDEX

[Bloodywog]

Hi All,

I just started playing bowling and I was asked to help maintain scores, averages and tournament scores.

I searched the forum and found similar table that I can use. The link is here

I have made a few adjustments.

1 - The is a handicap table based on a % of a scratch score. This depends on different tournament constitutions.
2 - I have 50 bowlers at moment, possible over 100 in near future. Every bowler is graded from Open, Seniors, A, B, C, D and Juniors. I have added a column for this.
3 - There is a column for Female and Male bowlers.
4 - I have added a few Tournament Leagues on bottom tabs, Adult All Event, Adult Singles, Doubles and Trios, Mixed Doubles & Mixed Trios.

I need help with a few things.

1 - In the tabs below, Adult All Event, Adult Singles, Doubles and Trios, Mixed Doubles & Mixed Trios, I have highlighted in red the players. I am trying to use a formula to find the respective players from the AdultDATA tab and place them from Highest Score to Lowest, in the correct grade and Male & Female groups.. The way the tabs are setup is the way Alley organisers would like it. Making this a little harder. Ive tried modifying this formula

=INDEX(AdultsDATA!$A$3:$A$52,MATCH(LARGE(AdultsDATA!$AC$3:$AC$52,1),AdultsDATA!$AC$3:$AC$52,0))

to also search "players", "Grade" and "Gender" and grouping them in right order.. Nothing seems to be working. I've used a search function based on cell entries A2, A29, A38 & A50 (grades) but can't make them work correctly

2 - In the Mixed and Trio leagues, I have the problem of identifying the players who bowled together and their in separate grades. Some players can also play multiple games. Using VLOOKUP won't work because there will be multiple same names. I figure SUMIF and/or RANK function may help with that.

I am thinking it be easier to have separate data for each respective grades, unless a formula can be used. I really hope this is clear! Thank you in advance to all who work on this.

[Pete_UK]

It would help if you attached your file.

Pete

[Bloodywog]

Sorry guys.

See attached

[Bloodywog]

Hi

I have added a formula for "Grade" column.

=IF(D3>=HandicapChart!$L$3,"Open",IF(D3>=HandicapChart!$L$4,"A",IF(D3>=HandicapChart!$L$5,"B",IF(D3>=HandicapChart!$L$6,"C","D"))))

This works great but the problem is that there are different averages for Males and Females. The above formula works great with Males, I don't know how to merge Male and Female into one formula.

Secondly. In the Adults All Events tab, I have used this formula in A4

=INDEX(AdultsDATA!$A$3:$A$53,MATCH(LARGE(AdultsDATA!$L$3:$L$53,1),AdultsDATA!$L$3:$L$53,0))

Is there a way to include only Open bowlers? Then I can use this formula to only include the other grade bowlers respectively.

I have being playing around with this spreadsheet for a while, it appears that it be easier if there a tabs for each Grade. What do you think?

[Bloodywog]

I have made more changes since last time. I only have worked on Adults All Events.

See attached.

These are the changes:

1. I added a new column and row (A:A, B:B & 2:2). A5 formula is

   =MATCH(B5,AdultsDATA!$AS$4:$AS$53,0)

2. C5 Formula is

   =INDEX(AdultsDATA!$A$4:$AD$53,$A5,C$2)

3. D5 Formula is

   =VLOOKUP(C5,AdultsDATA!$A$4:$AD$53,12,FALSE)

4. There is a new row to help with the match formula in cell C5, Adults All Events tab

Everthing seems to be working except for filtering only the respected Grades. Does anyone know how to do this? I have tried many things but getting errors.

[Bloodywog]

Hi (AGAIN)

I'm getting a little desperate. In regards to my above post (#5), the only way I can do this is to have separate tabs for each grade and each gender. The can work, but I end up with 30+ sheets. E.g. Mens Open, A Grade, B Grade, C Grade, Women Open, Women A Grade, Women B Grade etc. It will do for now i guess. I was wondering, If I placed all date in 1 sheet, maybe a macro to separate Male & Female bowlers and their grades. Does anyone know how to do this?

Many Thanks.

[Bloodywog]

*Bump* *bump*

[Pete_UK]

I can see that you have not had much help with this thread - there's only me asking for you to attach a file. I assume your latest file is that attached to Post #5, but I'm not sure what you want to do with it. Can you please explain what you are looking for?

Pete

[Bloodywog]

Hi Pete

Firstly, the main goal is to have a formula to find the respective bowlers from the AdultDATA tab and place them from Highest Score to Lowest Pins, then to the correct grade and by Male & Female groups.. These results are to be placed in the summary sheets (Adult Singles, Adult Doubles, Adult Trios). I have highlighted in red on the original post how "it should" look like.

On the previous post #5, I have worked out how to MATCH and INDEX by using ranking data on the far right (only in Adult All Events). This works great, but doesn't filter the Grade and Gender of all the bowlers to the correct groups. This won't work in doubles, trio or mixed. The only way I can think of is too have separate tabs for each division for both males and females and by grade. E.g. Mens_Open, Mens_AGrade, Mens_BGrade and so on. Once bowler found by INDEX and MATCH, then I use VLOOKUP to find the other data. If I have 200+ bowlers, I will end up with over 30 tabs (huge data). I like to avoid this if i can.

I have played around with few things and found more potential problems.

1 - I don't know how to isolate (pair) those bowlers in doubles, trios, mixed doubled divisions. Considering adding tabs for each of those divisions or maybe give each bowler and team a unique ID, then use a formula to group them somehow?
2 - In a unlikely situation, there was a draw (total pins), how do you separate those?
3 - In the Mixed and Trio leagues, I have the problem of identifying the players who bowled together. Some players can also attempt to qualify more than once, some situations have different bowling partners. Using VLOOKUP won't work because there will be multiple same names.
4 - Different averages grading in different tournament games. For example Men's - Open: >175, >A:151, >B: 135, >C: 120, >D:100. I can use a code like

=IF(C4>=175,"Open",IF(C4<=151,"A",IF(C4<=135,"B",IF(C4>=120,"C","D"))))

I hope this make sense. I can update the spreadsheet if you need better clarification. Any suggestions in sorting data (changing data layout_ or helping cut down tabs is appreciated.

[Pete_UK]

Okay, well it's Saturday afternoon here and I'm just about to go out, but I'll take a look this evening.

Pete

[Pete_UK]

I'm a bit confused with your ranking formulae in columns AF:AK (pale orange) and AL:AQ (green) of the AdultsDATA sheet. The orange formula incorporate a tie-break (which is good), whereas the green formula do not (bad) - are the green columns redundant?

I don't understand why you are ranking each individual game, both before and after applying the handicap - surely, you just want to rank the Totals (column L for Singles) ??

How do the Doubles and Trios games work? Are these the scores for each player in those competitions? Wouldn't you consider a pair of players for doubles, and combine their scores? If so, you need to devise a way of allocating the players into teams, and aggregate the scores for each team.

It's probably easier to just consider singles for the moment. I would suggest having one column for ranking of the total column, and then to split that into other columns depending on gender and Grade. Your report on Singles can then scan through the appropriate column. What are the categories of Grade? They seem to be Open, A, B, C, D and Seniors, but do you have any others?

Pete

[Bloodywog]

Pete

I have attached V3.0 with all updates and this will answer your questions. The data is all random. Some numbers may not add up

1/

I don't understand why you are ranking each individual game, both before and after applying the handicap - surely, you just want to rank the Totals (column L for Singles) ??

I forgot to mention, I was planning of having a POT for highest scores for each game. Plans changed, I like to keep this for the future but at this stage ranking by totals is ideal. See attached spreadsheet with all changes.

2/

How do the Doubles and Trios games work? Are these the scores for each player in those competitions?

Every bowler must enter tournament and need to bowl singles, doubles and trios to qualify for Finals. Yes, these are the scores for every bowler. In the summary sheets (Doubles, Trios & Mixed), I will consider a pair of players for doubles, and combine their scores. Same with Trios. Just a reminder - The summary sheets need to show both player scores for every grade because there's a $$POT$$ for highest scores combined bowlers & $$POT$$ for individuals scores for every grade in the doubles and trios event. That's why it's setup up like in attachment. I need to devise a way of allocating the players into teams, maybe a team# and also more columns (bowler A & bower B). If, for example, bowler 16 and bowler 22 (Opens) are going to bowl doubles together and they are the first doubles pair, they would be pair #1. One will be assigned the identifying number 1.1; the other gets 1.2. Thus the bowlers in pair 34 would be 34.1 and 34.2... Would you think that would work..? See Columns A-D.

3/

It's probably easier to just consider singles for the moment

Single tournaments are easier to manage, the combination of all three IS a difficult one. I'm open to suggestions but at moment I spend hours (too many hours) manually inserting data in individual places, grade, gender then organising them from highest o lowest scores then inserting them in summary sheet! It's a tedious process and I like to find another way. I'm thinking of having a data workbook and Summary workbook. Can data be look from different workbooks? If located is the same saved folder?

4/

What are the categories of Grade? They seem to be Open, A, B, C, D and Seniors, but do you have any others?

This is correct. In both Gender (Male & Female) there are only those grades. In near future, Girls and Boys will be included and as well and same grading (A, B, C, D).

I added new sheets for Qualifying and the finals. Please see notes on those sheets.

Any further questions.. let me know.

[Pete_UK]

To demonstrate how you can do this for singles, I've used column BB of the AdultsDATA sheet to rank on the total for singles, with this formula in BB4 :

=IF($E4="","",RANK(Q4,Q$4:Q$249,0)+COUNTIF(Q$4:Q4,Q4)-1)

Then in the following columns I have separated the ranks out into the appropriate categories and genders. I then rank those within each column with this formula in BP4:

=IF(BC4="","",RANK(BC4,BC$4:BC$249,1))

copied across and down. In the AdultSingles sheet I have this formula in B4:

=IFERROR(INDEX(AdultsDATA!$E:$E,MATCH($A4,AdultsDATA!BP:BP,0)),"")

which is also copied across and down to show the top-20 names in each of the categories. If you want the scores to go with the names you will need to insert a column between each of the columns I've used.

Obviously, this is a different layout to the one that you showed, but it achieves the same purpose in a more compact table.

Hope this helps.

Pete

[Bloodywog]

Pete

Awesome. Works really well for single tournaments.

If you want the scores to go with the names you will need to insert a column between each of the columns I've used

Then do I use a the LOOKUP function to get the scores?

In regards to the Grades column, refer to HandicapChart tab. I have the current formula

=VLOOKUP(H4,HandicapChart!A35:B336,2,FALSE)

to get thier averages (100% of 200). The grading for Mens and Ladies are different, how can we get this to work? At the moment I have

=IF(C4>=175,"Open",IF(C4<=151,"A",IF(C4<=135,"B",IF(C4>=120,"C","D"))))

but this is only for Mens not Women. I'm trying to include the cell (Mens/Women) in the formula but not working out.
Example: Mens Gradings-

• Open - Average >175
• A Grade - >151
• B Grade - >135
• C Grade - >120
• D Grade - >100

Womens Gradings-

• Open - >170
• A Grade - >150
• B Grade - >130
• C Grade - >110
• D Grade - >90

I'm still working on the doubles part. Trying to devise a way of allocating the players into teams, then sorting them our in ranks is a challenge.

I really appreciate your help.

[Pete_UK]

I've set up a slightly different table in the Handicap sheet (in blue) and used this formula to get the grades in F4 of the AdultsDATA sheet:

=IF(OR(G4="",H4=""),"",IF(G4="mens",VLOOKUP(H4,HandicapChart!K$11:L$15,2),VLOOKUP(H4,HandicapChart!N$11:O$15,2)))

so it now gives you the correct grade for men and women.

I've also swapped over the Mens and Womens ranking columns in that sheet.

I've changed the Adult Singles sheet to show the overall totals against each name - it's basically the same formula but you want to get the data from column Q (Total score). As there are twice as many columns, I've put the Womens' results below the Mens - easier on the eye, and easier to print out.

I've introduced a new sheet, Adult Singles_Long, which follows a similar format to what you had before. The trick here is to define the report for the top-20 in ALL categories, but then to use the filter on column N to hide all the rows with zero in (i.e. unselect zero on the filter drop-down), so that you only see the categories where there are some results. If you select All on the filter drop-down you will see what I mean. Columns M and N would not be included in the print area for this sheet.

I think you should rename the AdultsDATA sheet to Singles, as it contains all the data that you need to report on individuals. You could then have another sheet called Doubles (and one called Trios), where you can have a single entry for each team, made up of the data from the Singles sheet combined for both (or all three) players. Then for the team reports you can have similar reports as for the Singles, but you will be taking the data from the Doubles (or Trios) sheets.

Hope this helps.

Pete

[Pete_UK]

The formula for grade will not help to identify Seniors, and you have indicated that you might have Boys/Girls in future. It strikes me that you should have a column for Gender (with M/F only), one for Age (Senior,Adult,Child) as well as the grades. This will affect the formulae that you have already.

Hope this helps.

Pete

[Bloodywog]

Pete

That's is awesome! Crazy good. I'll look into the updated sheet tonight and let you know how I go.

[Bloodywog]

Totally forgot about seniors I agree, there should be a column for Gender, 1 for age and the Grades. Is the formula hard?

---

You could then have another sheet called Doubles (and one called Trios), where you can have a single entry for each team, made up of the data from the Singles sheet combined for both (or all three) players.

Just a lil confused. I do understand having all scores combined and setting it up like the Singles (Total Scores)then do the same but how do you get the team reports (doubles & trios) like Adult Singles_Long sheet? Dont you have to identify each pair (e.g. Pair 1, Pair 2 or Team #)in a different columnm, then rank them? Is this after you create the the Doubles (or Trios) sheets like you said, then using it to taking the data from the Doubles (or Trios) sheets?

Sorry. Still playinng around and understanding your previous formaulas (which I'm liking a lot by the way)

[Pete_UK]

Totally forgot about seniors I agree, there should be a column for Gender, 1 for age and the Grades. Is the formula hard?

It would affect the formulae which split the overall rankings into the appropriate categories. At the moment the formula looks at the gender and at the grade, but obviously if you change what is in those columns and introduce another column then these formulae would need to change also.

I've not given a lot of thought to the Doubles/Trios, but it struck me that you want a single record for each team and then you can do similar things as you are developing for the Singles sheet. I imagine that when you are setting this up then Player23 and Player 41, say, will come to you and say they want to be in a Doubles Team together, called "The Eagles". So, you have a start to creating the Doubles sheet - the team name, and the two players' names (to go into two separate columns) These can also be recorded in the Singles sheet. If you record every result in the Singles sheet against each individual player (for convenience of data entry), then the Doubles sheet can easily find the scores for Player23 and add them to the scores for Player41 to get a combined set of scores for the TEAM (I assume this is how it works). Then you can rank the teams within the Doubles sheet and for reports you can use formulae similar to the ones I have given you, but instead of looking at the Singles sheet (or AdultDATA sheet as it is currently called), they will look at the Doubles sheet. A similar approach can be taken for the Trios, but there you would have 3 players.

I feel I need to point out that I'm going away on Wednesday for 10 days, so I won't be able to help while I'm away.

Pete

[Bloodywog]

I imagine that when you are setting this up then Player23 and Player 41, say, will come to you and say they want to be in a Doubles Team together, called "The Eagles". So, you have a start to creating the Doubles sheet - the team name, and the two players' names (to go into two separate columns) These can also be recorded in the Singles sheet.

Yes Pete. More to that, players can have more than 1 team so instead of team name, team number is better.

During qualifying, there is $$$ involved for highest pair and also $$$ for each individual player (All Events- Singles, Doubles and Trios). Saying that, the more I think about it, I think the doubles and Trio sheets should be setup the way you suggest

Doubles sheet - the team name, and the two players' names (to go into two separate columns) These can also be recorded in the Singles sheet. If you record every result in the Singles sheet against each individual player (for convenience of data entry), then the Doubles sheet can easily find the scores for Player23 and add them to the scores for Player41 to get a combined set of scores for the TEAM

. Do the same with Trios. We can have a separate sheet called (All Events) and setup exactly like Adult Singles sheet and ranked each bowler with Overall Pins in top 20. See "AllEvent" tab

I have attached updated sheet for the doubles. Please see AdultsDoubles_Data and AllEvents sheets. Changes are in red. Is this what you mean? Like I said, I will record every result in the Singles sheet (I rename too something else than "Singles") against each individual player for convenience of data entry and have the way you mentioned above with the doubles and trios. How do you feel about that?

Notes:
1/ How do we determine a grade for a team? A column called "Team Grade" and "Team Average"? I'm thinking to have Doubles Averages A Grade – 330+, B Grade – 271 to 329, C Grade – 212 to 270 and D Grade - 211 or less and for the Trios Combined Averages A Grade – 660+, B Grade – 541 to 659, C Grade – 443 - 540 and D Grade - 442 or less
2/ Formulae which split the overall rankings into the appropriate categories - Gender (with M/F only), (Senior,Adult,Child) as well as the grades
3/ In AdultDouble_DATA tab - Do we use VLOOKUP OR INDEX/MATCH to find players, scores (all the data from "AdultDATA" tab?

Many Thanks Pete.

[Pete_UK]

I'll tackle some of your questions later on, but for now can you explain where the Avg and Games (columns E and G in the AdultsDATA sheet) data comes from? I can see that Avg is used to establish the handicap, but what is the Games data used for?

In terms of ease of data entry, you might find it more convenient to arrange the scores from each game to be next to each other, and then do the handicap calculations in 3 adjacent columns.

Pete

[Bloodywog]

the Avg and Games (columns E and G in the AdultsDATA sheet) data comes from? I can see that Avg is used to establish the handicap, but what is the Games data used for?

The Averages in Column E AdultsDATA) sheets are manually typed based on the players averages every 6months. The figures are worked out by tournament director. The "Games" in column G was put there to help with any "tie" scores (total). When a tie happens (rarely) the rank formula doesn't work. If you look at columns AT3:AW23, you see this. Unless you know another way to help remove a tie for the rank formula.

---

more convenient to arrange the scores from each game to be next to each other, and then do the handicap calculations in 3 adjacent columns.

Is this for the AdultsDATA or Doubles sheet..? I wait until you looked into it further.

Nick

[Pete_UK]

Hi Nick,

I meant to get back to you earlier, but I had things to do in readiness for going away tomorrow, and now I don't have much time to describe things. I've modified the workbook to include Gender/Age/Grade columns, and changed things around to a more logical sequence - pale yellow is for data input columns. I've also shown how you would need to set up columns to record team numbers - a pair of columns for Male_Doubles, Female_Doubles and Mixed_doubles. You would presumably have similar columns for Trios. I'll have to explain the seq column some other time - basically, it will help you to combine scores in the Doubles (and Trios) sheet.

I've amended the singles ranking columns and shown how you can get set up for Adults (as before) and for Seniors (though there is no data to demonstrate this). If you wanted Boys/Girls (which I called Child, but you might want U-16 or whatever), then you would have another 20 columns for them. I've not had chance to complete the reporting sheets.

I really must go now - I might check my computer before I leave at lunchtime tomorrow.

Hope this helps.

Pete

[Pete_UK]

In answer to your other questions in Post#20, you can use VLOOKUP only when the item you are looking up is on the left of the items you want to return, whereas with INDEX/MATCH you can look up on any column and return data from either the left or the right of it.

You can do what you propose for the grades for doubles and Trios, but you also need to remember Mixed teams. Just follow the layout that I used for the singles grades tables, and the formula that I used can be amended to suit the range where the tables are - I would think that you would have those formulae on the Doubles and Trios sheets.

Well, I hope you can progress with this in my absence - I leave in a couple of hours.

Pete

[Bloodywog]

Hi Pete

I know you are away and not able to reply to this. I just had few days off myself and had a good look last night.

I fully understand how you modified the workbook to a more logical sequence. The team sequences do make sense but I don't understand how it will help me to combine scores in the Doubles and Trios sheet. So I created the Doubles Sheet. I made the team name (or number) and the two players' names into two separate columns. I've renames the "Singles Sheet" to "OverallDATA". I've placed the team names/numbers into the columns you created. I had issues with the Doubles sheet finding the scores for both players and adding them to get a combined set of scores for the TEAM. PLEASE SEE THE ATTACHMENT WITH THE DOUBLES SHEET. In B4 do I use something like

=IFERROR(INDEX(OverallDATA!$A:$A,MATCH($A4,OverallDATA!G:G,0)),"")

. This code fails when you use it in column D. I can't get it to find the second player on team and third (trios). VLOOKUP was used in the other columns to retrieve the data. Is that right? The reports for the doubles sheet is easy enough to change, no problems there.

Some things popped up when playing with it.

1/ Grading a little different when you have doubles. So I put another column for "Team Avg" then used similar approach with grades. As we previously discussed, the formula will not help to identify Seniors, Boys or Girls. We should have a column for Gender (with M/F only), one for Senior,Adult,Child and the grades.
2/ For data entry, when a player does multiple attempts with different players in singles, doubles or trios, Do the name entries (cell) need to be different? Example Player 2, Player 2 (2), Player 2 (3)..? Do the formulas work on duplicate cell entries?
3/ When doing the reports, when you sort Singles data, the doubles report changes and same with trios. Will this not occur if data were separated into Doubles and trios sheets like I'm trying to do?

I will still play with it tonight, hopefully can work it out before you get home

[Bloodywog]

Hi

Just a quick one... What is the easiest way to get the rankings of all Grades (excluding Open) into one table? There will be a league based on all bowlers who are not in Open Grade, "Restricted" league with handicap.. Do I just add a extra column "Restricted" then follow same setup for the rankings and the "Adult Singles" sheet?

[Pete_UK]

Hi Nick,

I'm back, and I've amended your file from post #25 (attached). Various amendments, too numerous to list.

You haven't been very consistent with your allocation of names into teams - the M_double columns are for Male doubles teams, but you have some female in there. You don't need to confine team names to numbers - you can use normal words like "Aces", "Champs", "Bad Boys". Wouldn't your players like to choose their own names? Also, Boys and Girls are still M/F in the Gender column - it's just that their Age entry is Junior.

Anyway, check things out, as it's getting a bit late here.

Pete

[Bloodywog]

Pete

Thanks for replying. Hope you had a great break.

The allocation of names into teams makes more sense now. Works really well.

you can use normal words like "Aces", "Champs", "Bad Boys". Wouldn't your players like to choose their own names?

i assumed the formula was restricted to numbers. Team players may or may not wish to have a name "aces" "champs". We usually do, in big tournaments team numbers are used.. don't know why

In post #16, you mentioned the formula for grade will not help to identify Seniors or Juniors (in future). I inserted this into columns I:K (HandicapChart) sheet. Is this what you mean?

In post #25: When doing the reports, when you sort Singles data, the doubles report changes and same with trios. Will this not occur if data were separated into Doubles and trios sheets like I'm trying to do?

In your opinion, the current way i have it setup "OverallDATA" sheet, do you suggest having 1 sheet for Singles, 1 for Doubles, so on for all divisions...?

Really thank you in advance. I'm liking how quickly I put in data, then it sorts it out.. saved me soooooo much time!! Really appreciate it Pete. Throughout the next few days I will test run, find anything that may/may not rise in future.

[Bloodywog]

Pete

Thanks for replying. Hope you had a great break.

The allocation of names into teams makes more sense now. Works really well.

you can use normal words like "Aces", "Champs", "Bad Boys". Wouldn't your players like to choose their own names?

i assumed the formula was restricted to numbers. Team players may or may not wish to have a name "aces" "champs". We usually do, in big tournaments team numbers are used.. don't know why

In post #16, you mentioned the formula for grade will not help to identify Seniors or Juniors (in future). I inserted this into columns I:K (HandicapChart) sheet. Is this what you mean?

In post #25: When doing the reports, when you sort Singles data, the doubles report changes and same with trios. Will this not occur if data were separated into Doubles and trios sheets like I'm trying to do?

In your opinion, the current way i have it setup "OverallDATA" sheet, do you suggest having 1 sheet for Singles, 1 for Doubles, so on for all divisions...?

Really thank you in advance. I'm liking how quickly I put in data, then it sorts it out.. saved me soooooo much time!! Really appreciate it Pete. Throughout the next few days I will test run, find anything that may/may not rise in future.

[Pete_UK]

Hi Nick,

I've only just seen these responses of yours - the Forum has been playing up recently and I didn't get an email notification of your posts.

I'm not sure why you need to sort the data (other than for ease of data entry), but the formulae should work okay following sorting.

You could have one sheet just for data entry, so as well as the set-up details (pale yellow columns) you could enter the scores from matches as they occur. Then in the Singles sheet you could extract just the information for Singles from that data entry sheet and have your ranking formulae in there, similar for the Doubles sheet, and so on. Then your report sheets could just get the data from the appropriate secondary sheet. There are many ways of organising the data - you just have to settle on one that you are comfortable with and one that makes it easy to maintain the formulae.

Hope this helps.

Pete

[Bloodywog]

Pete

I was waiting for a while for your reply via email notification but it appears you did 10 days ago... so sorry. My firewall saying that excelforum is now an attack site for past few days, won't allow access unless I approve it..(weird)....

I have been playing with workbook and I understand there's 9 ways to skin a cat.. I think it is easier to keep one sheet for all the data and then use the INDEXMATCH formula to look for the values. At the moment the doubles and trio sheet is working great.

Some questions so far.
1. If I create a new sheet for "Singles", what is the easiest way to extract all the data? If we follow the same concept for doubles and trios (same columns), For column A (bowlers), do i use the "Paste Link" then fill down, the remaining columns use the index/Match to find all other data? If I have a reference cell for the index/match formula then it's easy.
2. The doubles summary sheet, I know how to duplicate it the way you did for singles with the Team Totals scores and rank them. I also need to display both bowlers scores.example.png
3. I can't seem to get the grade formula to work to find seniors and juniors. I have manually inserting them so far.

The sheet is almost coming to a close.. just need to do few more things before closing thread...

[Bloodywog]

Hi Pete

Did you get my PM..? My home PC saying excel forum is an attack site and wouldn't let me reply. I'm also not getting any email notifications when you reply.

If you didn't get anything, let me know. I retype the reply.

[Pete_UK]

Hi Nick,

yes, I got an email notification of your PM, but I couldn't get on the site last week due to the malware attacks. It has been very flaky since I got back, so I gave up trying to access it. If it is more stable now then I'll pick the thread up again.

However, it is a bit late now, so I'll take a look in the morning. In the meantime, do you want to post your latest version, along with any outstanding questions?

Pete

[Bloodywog]

Pete

Been away on leave, forgot to let you know. I will work on the spreadsheet over the week, will send updated version with all outstanding questions. Most likely be highlighted in RED throughtout the sheet.

Cheers,

Nick

[Bloodywog]

Hi Pete

Hope you been well.

I've attached the updated spreadsheet. All my outstanding questions will be highlighted in RED.

HandicapChart

• As we discussed earlier, Column J:M grade formula to work to find seniors and juniors in OverallDATA sheet (column F).

Singles Sheet

• What is the easiest way to extract all the data from "OverallDATA" to show the Singles scores? Do I use "PasteLink" into Bowlers column A then use MATCH/INDEX? I like to make the Singles, doubles and Trio sheets automatically display.

• Bowlers may have multiple attemps (singles, double & Trios). I need to record every attempt. Will this effect any formulas (duplicate names)? For example, some cases, Player 2 may bowl singles 4 times, Doubles twice and Trios three times.

• The summary Sheets, Do The "OverallDATA", "SinglesDATA", "DoublesDATA" and "TriosDATA" sheets need to be sorted Highest to Lowest in order for the Summary sheet to work?

AdultDoubles_Summary

• Column E - I need to display each bowler in the Team by their rank

• Will it be easier to have ALL Double data in one sheet, Do I Need to have separate sheet for Adults, Juniors, Seniors and Mixed?

Let me know if you have any issues/questions.

[Pete_UK]

Hi Nick,

good to hear from you again. Unfortunately, you have sent this through just before I am scheduled to go away for a long weekend (Friday to Monday), and I'm quite busy up till then so I'm not sure if I can spend anytime looking at this, as it will take quite a bit of time to familiarise myself with everything again.

I'll take a look next week.

Pete

[Bloodywog]

No worries Pete. Chat with you after your break.

Nick

[Pete_UK]

Nick,

it might be a good idea to bump this thread, say next Tuesday, to remind me about it - I'm sure there will be lots of other email notifications from other threads when I get back, so yours might get lost otherwise.

Pete

[Bloodywog]

*bump* *bump* *bump*

[Bloodywog]

*bump* *bump*

[Pete_UK]

Hi Nick,

December was a busy time for me with visitors each weekend in the lead up to Christmas, so I didn't get chance to look at your file. It seems so long ago, and there were so many questions in your last post (#35) - can you refresh me as to where you are at and what is outstanding?

Pete

[Bloodywog]

Pete

Welcome back. I understand.

Where we stand is still at post #35. I tried to work on the spreadsheet the past week and no luck. I only did some organising of data but nothing to fix the problems. Have you opened the attachment? I've put in "red" (including tabs) the problems I'm having.

How can I refresh you?

[Pete_UK]

[QUOTE=Bloodywog;3546397How can I refresh you?[/QUOTE]

I just wanted to know if you had progressed with this at all since post #35, as that was over a month ago.

I find myself quite busy with the day job at the moment, and a lot of my evenings and weekends are taken up with a major upgrade to the kitchen which my wife has initiated (there was nothing wrong with the old one !!), and as your task is quite a big problem I can't guarantee when I'll have enough time to sit down and go through it all. I'll pick it up when I get chance, but if you do make any changes yourself then please update this thread.

Pete

[Bloodywog]

Pete

I worked on this for 10hrs straight yesterday and 6 hours today. I got the doubles and Trio summary sheets to work. Maybe not the "best" way but I guess there is 9 ways to skin a cat. I have attached it.

I have done many little changes but I stil can't work out some liitle things. Refer to Post #35. Help needed is in "red" in the attachment.

HandicapDATA Sheet

• Column J:M grade formula to work to find seniors and juniors in OverallDATA sheet (column F).

Singles Sheet

• The easiest way to extract all the bowlers data (Column A) from "OverallDATA". I just used the =to cell reference. Does this matter?

• Bowlers may have multiple attemps (singles, double & Trios). I need to record every attempt. Will this effect any formulas (duplicate names)? For example, some cases, Player 2 may bowl singles 4 times, Doubles twice and Trios three times.

Doubles & Trios Summary Sheets

• In coloumn B & G, can we lookup both H & J columns in OverallDATA or do I need a seprate sheet for Male and Females?.

Thanks in advance Pete.