Guide
Cover Sheet
1 Clicking on it, the language of the book can be changed.
Members Sheet
1 It have to be introduced the link shown by the navigator when one is in the page of the alliance where all alliance members are displayed. As an example, to see which page we are referring to, this Excel has already a link included, so it can be pasted on the navigator to see which link is needed. Without the proper link, none of the macros of this Excel will work.
2 This table is used to calculate how much should pay each member according to their level. In the image, a multiplier of 1 have been assigned to lv100+. Therefore, in the table 6, every level100+ contained in the column called level is multiplied by 1 obtaining the adapted level (forth column of the table6). Another example, a multiplier of 0.5 is assigned to members from levels 90 to 99, so Waw0197 (level99) have an adapted level = level * multiplier = 99 * 0.5 = 50. The result will be always rounded up.
To consider members below an X level, should be written a multiplier in the last row of the table2, yet, be careful though none of the rows in the middle should be empty, otherwise the calculations will not work. A little trick to avoid this issue is to copy a same level and multiplier several times to fill all the empty cells of the table until get to the last row. The row we really want to fill.
3 In the first row of this table, in the light grey cell can be seen the total amount of gold that should be paid by the alliance to upgrade a building. This amount is returned by coding. In case one want to introduce this amount by hand, can do so writing it on the blue cell at the right hand.
In the second row can be seen the donation per level or the unitary donation. This amount is obtained from dividing the total donation by the addition of all the member’s adapted level.
Finally, to calculate the total donation that should be paid by each alliance member (fifth column of table6), it is multiplied the unitary donation by the adapted level of each member.
4 Button, ‘Add Member’, add more members at the bottom of the table 6. This button is useful in case it is needed to add some more members from the outside of the alliance (from a reserve alliance, for example)
Button ‘List Members’, this button is used to sort members from the highest level to the lowest. This button is ideal to be used when we have already added more members using the ‘Add Members’ button.
5 By clicking on that button it will be automatically gathered all members of the alliance, the link to their profiles and also their levels. Alter that the information is show in the table 6. This button uses the link introduced before (point 1).
6 Table 6:
Excluded column: Introduce an ‘X’ to avoid that member to donate. Alter that, the amount of gold that should be paid by each member will be automatically recalculated.
Members and Level columns: both columns can be edited by hand even though they are not blue painted.
Paid Column: Here can be written all payments that each member have already done. With a right click, an user form will be displayed. Every amount introduced in this form will be added to the amount that already were in that cell. It can also be introduced negative amounts.
When the payments done by each member are equal to the total amount of gold to be paid, the fonts of the payments will be green painted.
In case the amount paid is more that the amount of gold to be paid, then, the fonts will be bold green painted. In addition, the difference between the amount to be paid and the amount already paid will be shown in the next column (Overpaid Column).
Buildings Sheet
Every blue painted cell can be edited either by hand or by macro.
1 Pressing that button it will be automatically gathered the level of all buildings of the alliance (second column of the table 2). In addition, it will gather the label, name, rank and fortune of the alliance.
This button will work only with alliances that are at least top 50.
2 In the third column have to be introduced the number of levels we want to upgrade that specific building. For example, we already have the Forum at level 12 and we want to upgrade it to level 14. Therefore, the number that we should introduce is 2.
In the fourth column it is displayed the total amount of required gold.
In the fifth column it is subtracted the centurion discount and ‘other discounts’ ( it could come upgrading discounts events in the future) to the total amount of gold displayed in the fourth column. Now the total amount of gold to be donated is shown in bold at the lower right hand corner of the table (in this cell is also subtracted the fortune of the alliance). The amount of gold shown in this cell is the amount that will be used in Members Sheet.
3 The centurion discount and Other Discounts must be placed by hand. The button 1 will not gather this information.
Other
When several alliances want to donate. From reserve alliances, for example.
1 Create a copy of the Excel. Open both books (In case it is not possible, just make sure their names are not the same).
2 Run the ‘Get Members’ button in both Excels.
3 In just one Excel push the ‘Add Member’ button (Members Sheet).
4 Copy and paste from Excel 2 to Excel 1 all members and their levels (Only second and third columns).
5 Push ‘List Members’ button.
