wow spreadsheet

guild roster fix

workaround

There's an issue with the Auto Guild roster that causes people to be marked as the wrong spec. This is an issue in the Guild API, on Blizzard's side marking people wrong even if their character API is showing up right. This is a work around, hopefully in the future Blizz gets their act together and fixes it on their own.

Known issue: Because Role sort is based on the Guild API's role, characters will be sorted by the role given in the Guild API, so they may be a bit mixed up.

Steps

  1. Use the View menu and select Hidden sheets (1) > Drop downs to unhide the sheet.

    View menu, Hidden sheets, Drop downs

  2. Paste this list into E1, filling out column E and F.

    The spec list pasted into columns E and F

  3. Right click on the Drop downs tab and Hide sheet. ...We don't want people to know of its SECRETS

    Hide sheet in the tab menu

  4. Right click on the C at the top of Column C, and select Insert 1 right.

    Insert 1 right

  5. In our new column, D, select D7 and paste the following formula:

    =if(isblank(B7), "", if(AND(H7="Frost",F7="DeathKnight"), "Melee", vlookup(H7,'Drop downs'!E:F,2,false)))
    Formula pasted into D7

  6. Copy this formula all the way down by selecting the cell. There should be a blue square in the bottom right corner of it. Click and drag it down to copy it (simply copy/pasting it won't work, because we need it to change the row # each time!)

    Dragging the formula down the column

  7. If the fancy colors did not copy over to Column D from C, then you can select a cell in Column C that has the fancy colors, then click this Paint format button, and then drag it over Column D to paint it all fancy.

    Paint format button

  8. Right click on the C in column C and select Hide column. Because it's wrong, and we don't want people to know of its shame.

    Hide column

  9. Lastly we need to update the formula counters in these cells:

    The role counter cells

    They should be replaced with these:

    =concat("Tanks: ",countif(D:D,"Tank"))
    =concat("Healers: ",countif(D:D,"Healer"))
    =CONCATENATE("M:", countif(D:D,"melee"), " R:", countif(D:D,"ranged"), CHAR(10), "Total:", countif(D:D,"melee")+countif(D:D,"ranged"))