Excel help please

EchoLocation

bazster wrote: »

But he's got you doing it for him because he doesn't know how to do it in Excel either. If he needs to learn he might as well learn how to do it properly, and anyway, in my opinion learning to construct a simple database is considerably easier than learning some of the Excel features you are proposing to use.

Which is why I'm helping and constructing it in as simple a way as possible to fit the needs of the project rather than leave him messing around with a bloated fudged template. I will also explain the approach I have taken and the few things that need to be explained in order to manage the file going forward. The most complex this file will get is an array formula which is not diffcult to explain, particulary when it can be replaced by a simpler SUMIFS function.

If you think this project warrants learning Access, which the OP probably doesn't have anyway, over a few simple Excel concepts - which will be of more benefit - I think you're the one with the hammer.

martinthebandit

Having had to use both in the past I know which one I would choose, and its not access

EchoLocation

I may as well upload this now before I think too much about it and start making OCD changes. Darts League - Final.xls. I assume as you uploaded your original you've no problems with me posting the link here, after all it'll give anybody else interested an opportunity to have a look and make observations of any oversights I've made or suggestions for changes.
____________________________________________

As mentioned before I have had to do this in LibreOffice, although I have saved it as an .xls, so there may be a few things that look different when opened in Excel.

The couple of thing I know of are - you may get an unsigned file warning when you try to open the file initially, if you do just click ok to open and then save the file, this will ensure the warning is not shown again. There are no macros or code in this file so it's nothing to do with that.

Secondly, when I've opened LibreOffice files in Excel in the past, each worksheet had a 'Split' at the very top of the sheet. These can be removed by using the 'Split' option in the 'Window' menu, or just double clicking the split line. You may need to select the split option twice or double click twice to fully remove them. If you're not sure what a split is or looks like, open a blank worksheet and select the option from the Window menu. You'll see it's a thin grey bar across the sheet that allows you two split views of a file. Look for this but right at the top of the sheets.

The only other things are that I seem to remember the option to show/hide the grid lines doesn't carry over to Excel, so you may need to turn off the grid lines on the 'View' Tab by unticking the box for gridlines. It's not a necessity it just keeps the sheets nice and white and clean. Similarly, in LibreOffice I can only turn the column and row headers on and off for all sheets at once, so I have left them on for all sheets but you may wish to turn them off for more space, at least on the 'League' sheet, but again not a necessity.
___________________________________________

I've got a 22" widescreen monitor so I'm not sure how the sheets will fit the width of your screen. The League sheet is really the only one that you may want to fit, so you can adjust a few columns/text sizes if you want to do this.

The colours in LibreOffice may not match those in Excel's standard palette, so if you make any changes you may not be able to find the matching colour without using a custom colour - because of this I've not really gone to town on the formatting outside of the League sheet so feel free to change any of it.
____________________________________________

As detailed before there are 5 sheets - Players | Results | Totals | League | Variables

I'll go into more detail about these in another post - although I'll detail the Results sheet briefly below - but they are basically as I described before. For the moment you just need to use the results sheet and the Totals and League sheets will automatically update. I've added some dummy results for each player so you can change these and see how the other sheets update.

The only other change you'd need to make in a season is on the Totals sheet, where you can select a season 'result' for each player - these are shown as abbreviated codes (RG = Relegation etc) but you can add the key to what they are at the bottom of the League sheet if you wish. I've added these to the Premier table as an example.

Results Sheet

As a single match has 2 players, I have set the formatting so you can add both players results and it is easy to separate them visually from the next match.

Dropdown menus have been applied to the columns - Player Name, Opponent Name and Match Type.

Formulas have been added to the columns - Division, Bonus, Deduction and Result. if you add new results to the sheet you will need to drag these formulas down.

The other columns you can enter either a Date or Number (Legs/Checkout/180's).

The columns - Short Legs and 80+ Checkouts, are single columns, but if you separate the values in each by a comma it should be easy at a later date to split them out for analysis.

There is a free type column for any match comments that you want to record.
____________________________________________

There's a couple of decisions I made that I'll need to explain the reasons behind, obviously along with the details of the various formulas - don't be frightened by any of them, they really aren't as complicated as you may first think.

But for now I'll let you have a look and play with it first and see what questions or changes you come up with. I hope I've understood it all correctly but if not I'm happy to make any changes. Anbody else reading this feel free to chip in with any comments or changes as I admit I've not had the time to fully spec and test this as I would a normal project.

slim1690

Wow, that's pretty awesome, but I'm totally clueless as to how to change anything, for example the order the league table columns are displayed in, and also is there a way to make it output a separate table for total 180s, high checkouts and short legs? It also appears to award league points when I input Challenge Cup results. Apologies for being so useless at this!

EchoLocation

Ok here's a new version - Darts League - Final 2.xls

the order the league table columns are displayed in...
On the League sheet in the very top black bar (just above the red bar) you can change the header names and the subsequent league tables will update to reflect the new header. Just ensure that the header you add is exactly the same as it appears on the Totals sheet - for example, on the League sheet try changing the header Deduction to 180's and you should see it work.

is there a way to make it output a separate table for total 180s, high checkouts and short legs?
I have added a 'Stats' sheet to show this data separately, although I have left the 180's and high checkout on the League sheet as well. I can remove it if you want. As for short legs, my understanding was that you recorded ALL the short legs for each player, hence my instruction to record them in a single columns separated by comma's. If, however, you only record the shortest leg - like the highest checkout - you can just add the single value on the Results sheet. The short leg is also displayed on the 'Stats' sheet. If you do still want to record all short legs I can add a column back in to record them separated by a commma (as per the 80+ checkouts) but you can still enter the 'shortest' leg in it's own column.

It also appears to award league points when I input Challenge Cup results.
Sorry my oversight, didn't consider that these results shouldn't count in the League. I have altered the Totals sheet so that it currently only looks at the results with a Match Type of League, Premier Semi, Promotion Playoff, Relegation Playoff and Grand Final. If you don't want all of these to count toward the league totals, look on the Variables sheet and change the value against the particular Match Type from 'Include' to 'Exclude'. You will see that currently they are all Include apart from Challenge Cup. I can set up a separate sheet with tables for the Challenge Cup results if you want. The 'Stats' sheet DOES currently include the Challenge Cup results.

Apologies for being so useless at this!
No need to apologise, nobody know's anything without first learning it. As I get around to explaining a few simple Excel concepts things will gradually make sense.

For example, this probably looks terrifying...

{=IF($B9="","",SUM(IF($Results.$B$3:$B$500=$B9,IF($Results.$H$3:$H$500="Include",IF($Results.$K$3:$K$500=F$8,1,0),0),0)))}

...but it's really not. It's actually a staightforward logical structure once you understand a few basic things. More of this to come...

slim1690

That's superb, I'll have a proper look at it after I get home tonight, got a tournament to run this evening.

Thanks

EchoLocation

Apologies, in my haste I uploaded an incomplete file. Try this one - Darts League - Final 2b.xls

slim1690

I'm pretty comfortable with this now, may have another little tweak or two required (such as adding an extra Division etc) but I'll find that out next week when I start using it for real.

For clarification, the Checkouts and Short Legs columns are both supposed to be comma separated, and output a table of ALL the checkouts and short legs, rather than just each individual's best, because for example if two players tie for top checkout on 160, we look to their 2nd highest to decide who gets the trophy, similarly with the shortest leg trophy.

EchoLocation

Ok, to add another Division...

1) On the Variables sheet - Click into the Divisions list, insert a row, enter the new Division.
Be sure to insert the row WITHIN the list rather than at the end as it is a named range and the new row, if added to the end, may not be included in the named range.

2) On the Players sheet - Do the same as above for x number of new rows, then you can select the new Division for each new player, and then simply sort the list by Division then Player. Of course if you'd rather just have them in Player name order then that's fine.

3) On the Totals sheet - highligh and copy one of the Divisions (eg. Rows 59 to 70) and then click on Row 72 and paste it below Division 4. Then you just need to select the Player names from the dropdown list. The Division will be automatically be picked up. If there are any player names left from the copied rows just delete the name, formulas will blank themselves.
Technically the names can be in any order, but I've kept them in alphabetical order as the ranking will, in the event of matching points, rank them in the order they appear on this sheet rather than have a duplicate ranking (this is something that can be changed to allow duplicate ranking if you prefer).

4) On the League sheet - as above, simply copy one of the Leagues (eg. Rows 61 to 73) and then click on Row 75 and paste it below Division 4. Then you can just change the Division name above the Player name (Although this is just a label and not used by any formulas). The Player names and results will remain the same as the Division you copied, so you now need to replace the cell range in the fomulas so it picks up the new details - this is the only fiddling with formulas you'll need to do.
The formulas on this sheet are simply lookups that refer to the appropriate cells on the Results sheet - we need to make sure that the formulas in this newly copied Division are looking at the cells in the newly copied Division on the Totals sheet.

Have a look back on the Totals sheet and note the row numbers that contain just the results (not the headers) in your new Division. If you have followed the above examples, this should be rows 74 to 83. Now, back on the League sheet click on the first player name in your new Division (as mentioned above it will still be the same as the Division you copied) and copy the cell range that the vlookup formula is using, in the case of the example it shoud be - $A$61:$U$70 - now copy this, then highlight the row in the new Division that hold the reults (77 to 86) then select Find and Replace from the Edit menu and Replace ALL... $A$61:$U$70 with $A$74:$U$83 ...if there are any player names left over from the copied Division you will see errors appear against their name, DO NOT delete the name but simply delete the gray Rank number from the left hand side and the Player name and errors will clear.

Just FYI ,the reason I have given 10 rows to each division is simply to allow for any additional players in the future. The way to do this is once you have added them to a Division on the Totals sheet, you can then reverse the above action and add the rank numbers back in to the left hand side and the Players/Results should be automatically picked up.

__________________________________________________

In terms of the Short legs/Checkouts, I would say yes keep two columns to record all Short legs/Checkouts separated by commas. But maybe you can have 3 columns to record just the 1st, 2nd and 3rd lowest/highest for each as well to save having to sort though the comma separated values to find the next lowest/highest.