guild roster fix
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
Use the View menu and select Hidden sheets (1) > Drop downs to unhide the sheet
Right click on the Drop downs tab and Hide sheet ...We don't want people to know of its SECRETS
Right Click on the C at the top of Column C, and select Insert 1 right
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)))
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!)
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.
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.
Lastly we need to update the formula counters in these 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"))
