Skip to content

Instantly share code, notes, and snippets.

@arlm
Created December 12, 2023 12:15
Show Gist options
  • Save arlm/6ea2178310b5474bfc540fbbfd620ceb to your computer and use it in GitHub Desktop.
Save arlm/6ea2178310b5474bfc540fbbfd620ceb to your computer and use it in GitHub Desktop.

=AND(COUNTIF(indirect("R"&ROW()&"C"&(COLUMN()+1), FALSE),indirect(MID(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)),2,8)&"$F"&RIGHT(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)), LEN(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)))-11)))=0, COUNTIFS(indirect(MID(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)),2,8)&"$F"&RIGHT(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)), LEN(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)))-11)),"<>"&"", indirect(MID(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)),2,8)&"$F"&RIGHT(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)), LEN(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)))-11)),"<>"&"""""")>0)

Brackets

Colored assignmenmts

BK9:BK10,I9:I10,BK17:BK18,I17:I18,BK25:BK26,I25:I26,BK33:BK34,I33:I34,BK41:BK42,I41:I42,BK49:BK50,I49:I50,BK57:BK58,I57:I58,BK65:BK66,I65:I66,BK73:BK74,I73:I74,BK81:BK82,I81:I82,BK89:BK90,I89:I90,BK97:BK98,I97:I98,BK105:BK106,I105:I106,BK113:BK114,I113:I114,BK121:BK122,I121:I122,BK129:BK130,I129:I130,BE13:BE14,O13:O14,BE29:BE30,O29:O30,BE45:BE46,O45:O46,BE61:BE62,O61:O62,BE77:BE78,O77:O78,BE93:BE94,O93:O94,BE109:BE110,O109:O110,BE125:BE126,O125:O126,AX21:AX22,V21:V22,AX53:AX54,V53:V54,AX85:AX86,V85:V86,AX117:AX118,V117:V118,AB37:AB38,AB101:AB102,AR37:AR38,AR101:AR102

Player Names

BL9:BL10,BL17:BL18,BL25:BL26,BL33:BL34,BL41:BL42,BL49:BL50,BL57:BL58,BL65:BL66,BL73:BL74,BL81:BL82,BL89:BL90,BL97:BL98,BL105:BL106,BL113:BL114,BL121:BL122,BL129:BL130,BF13:BF14,BF29:BF30,BF45:BF46,BF61:BF62,BF77:BF78,BF93:BF94,BF109:BF110,BF125:BF126,AY21:AY22,AY53:AY54,AY85:AY86,AY117:AY118,AA37:AA38,AA101:AA102,AS37:AS38,AS101:AS102

Black (Winner)

=OR(COUNTIF(indirect("R"&ROW()&"C"&(COLUMN()+1), FALSE),indirect(MID(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)),2,8)&"$F"&RIGHT(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)), LEN(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)))-11)))>0, COUNTIFS(indirect(MID(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)),2,8)&"$F"&RIGHT(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)), LEN(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)))-11)),"="&"", indirect(MID(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)),2,8)&"$F"&RIGHT(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)), LEN(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)))-11)),"="&"""""")>0)

Gray (Defeated)

=AND(COUNTIF(indirect("R"&ROW()&"C"&(COLUMN()+1), FALSE),indirect(MID(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)),2,8)&"$F"&RIGHT(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)), LEN(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)))-11)))=0, COUNTIFS(indirect(MID(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)),2,8)&"$F"&RIGHT(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)), LEN(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)))-11)),"<>"&"", indirect(MID(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)),2,8)&"$F"&RIGHT(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)), LEN(FORMULATEXT(indirect("R"&ROW()&"C"&(COLUMN()), FALSE)))-11)),"<>"&"""""")>0)

Conditional Formula for checking the victory points

=AND(AND(indirect("winner") = "USSR",indirect("played") = "Y"), AND(indirect("USSR_points")<indirect("NATO_points"), NOT(OR(ISBLANK(indirect("end_mode")),indirect("end_mode") = "Concession"))))

=AND(AND(indirect("winner") = "NATO",indirect("played") = "Y"), AND(indirect("NATO_points")<indirect("USSR_points"), NOT(OR(ISBLANK(indirect("end_mode")),indirect("end_mode") = "Concession"))))

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment