Using Your Spreadsheet. (18/01/2009 06:07)

This is a three part blog: Introduction, Setting Up Your Own Excel Spreadsheet, and Using Your Spreadsheet (this page).

Bring the Spreadsheet up to speed.

If you have completed the task of setting up the spreadsheet from the second part in this blog, then you will have a spreadsheet that is functional. To bring the spreadsheet up to date you will have to change the data in Section 1 to reflect the attributes of your players at the start of the season. Go to a list of your team in ML and bring up the pop-up window of your first attacker listed in the spreadsheet. You have to manually change the data in Section 1 by deducting the gains of the player this season. One of my players currently has the following and to the right is what should be entered into Section 1 under the player's row:

Attribute		Diff	Indicator		Season start attribute entered in Section 1
Quality	90	+2			88
Keeping	48	+2			46
Tackling	87	+3			84
Passing	94	+2			92
Shooting	87	+2			85
Heading	74	+4			70
Speed	88	+1			87
Stamina	92	+1			91
Perception	83	+1			82
Fitness	99

Once you have completed entering the season start data for a player in Section 1, you can compare the values in Section 4 (to the right of Section 1) to the values in the Diff column of the player's pop-up window and it should read in the example above:      2    2    3    2    2    4    1    1    1    
This should ensure you are entering the correct data.

Buying a player.

The first player's row in Section 2 must always be on row 37 as the formulae and macros work on exact placement of cells and any movement must be compensated with the assistance of deleting or inserting blank rows. If you are buying a player then you must insert a row in all sections. Start by adding a row in section 1 with consideration of the players position and the alphabetical order of players in that position (eg if you buy a GK named Noel Griffiths then you must insert a row in between the Goalkeepers named Adam Swan and Xavier Marshall). To insert a row just below row 18, you will click on the 19 of row 19 on the left to select the whole row and then go to Insert -> Row (or use the right click of the mouse). Inserting a row in Section 1 (and Section 4 in the same process)  will move section 2, 3 and 5 down a row. Therefore you must delete a row to keep these lower Sections in their correct positions. I would delete row 33 (the bottom formulae in Section 4 should be in row 34 after inserting a row) to counter the moving down of the other sections. You can leave all the data blank as long as you insert and delete the appropriate rows, then you import the current data from your Individual Training page into the spreadsheet and update the data in Sections 1, 4 and 5 from the imported data in Section 4. Section 1 requires the season start attributes entered (a teamless player can have his data copied directly from Section 4) and Sections 4 and 5 require the name and position of the player entered. You could also update the Pos column so that it counts properly down the column in each section. In section 4 you can just select the row of a player in the same position between columns U and AF and just drag the little square on the bottom right down or up so that all the colours and formulae are copied in two moves.


Selling or sacking a player.

When you sell or sack a player you must delete the rows that the player occurs on in each section. Select the rows from their numbers on the left so that the whole row is selected and right-click the mouse and select delete or go to Edit -> Delete. To keep the lower sections in their correct positions after deleting a row, you must insert a row in between each section. Select the row that the formulae are on in Section 4 and insert the row and insert a row below Sections 2 and 5 as well.


If the inserting of and deleting of rows is not done before importing new data with a different squad (ie a player bought, sacked or sold) then everything will be messed up and the best solution is to close the workbook without saving it and trying to get the row insertions and deletions correct before importing the data again.

I have not created a macro to deal with buying, selling or sacking players but I may look at it in the near future if I can find some time as some people are asking me about this spreadsheet from a comment I made in the forums.

If you took the time to set-up a spreadsheet using this blog, then I hope you find it useful



                       Bozo.