This is an spread sheet in Excel, keeping track of the referee's matches, how much they have earned and generates a milage report for monthly reimburement.
The games are listed on the internet, so the Excel sheet imports from there, sorts the games first by date, and then by time. Lastly it also lists location for the game. This data is listed on one sheet in excel, and then referenced into another sheet. Here the referee lists if he was alone or not on the game, choose from a drop down menu, and the sheet then calculates his earnings. Finally, he can also generate a milage report, in the milage sheet, by selectiong the month, all his different locations are then listed by date.
**To be able to get a clear understanding please read the full description.** This is specifically for Microsoft Excel 2007, xlsx compatible format. Any prior versions or xls compatibility will NOT be accepted. A prototype with most code functioning can be downloaded here, it's Anti-Virus scanned with definitons of today, but it is still recommended that you do it on your own machine too. RentACoder only takes zip files, so the excel file is in zip format. Please download [url removed, login to view] to better follow along.
This is an application for the referees in floorball in Stockholm, Sweden. To import each referee's games go here: <[url removed, login to view]>
Login with user id: 150799
and password: 19820801
Then you will se a list of the games assigned to that referee. On the webpage the information is contained within a table, allowing for easy imort via Mircosoft Excel's own import from Web function. The columns are fairly easy, starting from the left, Match number (Matchnr), Division (Serie), Date (Datum), Time (Tid), Home (Hemmalag), Gone (Bortalag), Location (Plats) and finally Function (Funktion).
The first part which is tricky is that games may be added or removed, changed or cancelled during a season. So the import page needs to be able to reflect that, by updating accordingly, this update does not need to be automatic, it's fine if one needs to click on Excel's update feature.
Now we also need to import the payment schedule, listed here: <[url removed, login to view]>
See table called "Seriespel".
Then we have the payment sheet, where the referee will choose from a drop down menu if he was alone on that game or with a collegue. *I just need the function to input whether alone or not, it really don't need to be a separate sheet, open to suggestions here.
*The tricky part here is that some series are called P932S for example. P is for boys and F is for girls, then age then division. This one, P932S would read out, Boys born 93, division 2 South. Then there are multiples like, P932M for Middle and also P932N for North. But as you see in the payment schedule, only the age is listed,? nothing about the North, South or so. So one needs to create a function in Excel where it compares the Division and finds the right amount for that game in the payment column. If the referee was alone, it will be 1.5 times the normal paymant for that game.
Then we come to the summary page, here games are summarized by month, how many games have this referee handled in this month, total pyament received, and an average per game is calculated. Maybe one could enchance this with Pivot tables, I don't know since I'm not very familiar with them. But a requested function, added to the prototype available for download here, is a function showing how many times each referee has been handling a game for that club and how many times each team of that club has met the specific referee.
Finally we have the milage report function.
The referees are getting reimbursed for travelling expenses. They write up a report, fill in number of miles for that day and then print it, sign it and send it? and send it in for approval. Here one should be able to select a month, and then a report would be generated. A template for the report is also included in the downloadable zip file, please open it now.
The? fields which are marked in red are the ones that needs to be autofilled. First by date, just the day number, like 15 is fine. Then the report needs to take the matchnumber of the first game in each Gymnasium for that day being reported. They are to be filled in with a "space-space" in between them. Next up we have the name of each Gymnasium, same deal, listed in the order the referee went to them, also separated by "space-space". If a referee has had more games than the report template allows for, it needs to create a new report page. It can be one long Excel sheet, with proper page breaks also. And the travel report template needs to be in the same work book as all the other sheets, it's just a side template right now.
Somwhere the function to store milage travelled and if that game/gymnasium should be listed. Sometimes the referees share ride to the game, then only one of them gets reimbursed. So When the milage report generates, it would also need to exclude games/gymnasiums on days when they have shared a ride. There also needs to be a place to input the amount of kilometers for dates when you are entitled to reimbursement. And then when generated, they will show up in the appropriate box. Right now there is a column in the "Games" sheet, where one can add number of km travelled. Using conditional formatting, that if that box is greater than zero, it would then cause for the travel report to enter that date into the report?
Please do let me know!