# How to auto-populate matrix



## alm395 (Dec 15, 2022)

Hi,

I have two separate questions.  

1. How would I go about having my matrix quadrants auto-populate once the quadrant is identified on the associated table?  I have written several formulas, but keep receiving duplicate "Goals" appear. I have added a second matrix to my workbook to illustrate how I would like it to appear.

*Formula I used
Displayed in the first matrix on the "Quick Wins" quadrant (cells C5:C19). Notice multiple duplicates.*
=IF(N6="","",IF(N6="Quick Win",K6,IF(N7="","",IF(N7="Quick Win",K7,IF(N8="","",IF(N8="Quick Win",K8,IF(N9="","",IF(N9="Quick Win",K9,IF(N10="","",IF(N10="Quick Win",K10,IF(N11="","",IF(N11="Quick Win",K11,IF(N12="","",IF(N12="Quick Win",K12,IF(N13="","",IF(N13="Quick Win",K13,IF(N14="","",IF(N14="Quick Win",K14,IF(N15="","",IF(N15="Quick Win",K15,IF(N16="","",IF(N16="Quick Win",K16,IF(N17="","",IF(N17="Quick Win",K17,IF(N18="","",IF(N18="Quick Win",K18,IF(N19="","",IF(N19="Quick Win",K19,IF(N20="","",IF(N20="Quick Win",K20))))))))))))))))))))))))))))))

2. When the status changes to "DONE" on the table, I would like the associated "Goal" to remain listed on the matrix, but be crossed out. (See the attached image for "Goal 1" and "Goal 8" on the second matrix. For some reason it won't display on the XL2BB table I copied over.)

*Formula I used*





Thanks so much for taking time to help!  This is such an amazing resource & community!!! 🙌

Matrix Test.xlsxBCDEFGHIJKLMNOPQ3THIS IS HOW I WOULD LIKE IT TO LOOK4Low EffortHigh EffortLow EffortHigh EffortACTIONSFinished Tasks5High ImpactGOAL 1High ImpactGOAL 1GOAL 4#DescriptionImpactEffortQuadrantStatusCross out6GOAL 3GOAL 3GOAL 51GOAL 1HighLowQuick WinDONE7GOAL 3GOAL 13GOAL 72GOAL 2LowLowFill-InASSIGNED8GOAL 13GOAL 83GOAL 3HighLowQuick WinCREATED9GOAL 134GOAL 4HighHighMajor ProjectON HOLD10GOAL 135GOAL 5HighHighMajor ProjectASSIGNED11GOAL 136GOAL 6LowLowFill-InCREATED12GOAL 137GOAL 7HighHighMajor ProjectCREATED13GOAL 138GOAL 8HighHighMajor ProjectDONE14GOAL 139GOAL 9LowHighThankless TaskASSIGNED15GOAL 1310GOAL 10LowHighThankless TaskON HOLD16GOAL 1311GOAL 11LowLowFill-InCREATED17GOAL 1312GOAL 12LowLowFill-InIN PROGRESS18 13GOAL 13HighLowQuick WinIN PROGRESS19 14 20Low ImpactLow ImpactGOAL 2GOAL 915 21GOAL 6GOAL 1022GOAL 1123GOAL 1224PARKING LOT25 26 2728293031323334MENU (2)Cell FormulasRangeFormulaC5:C19C5=IF(N6="","",IF(N6="Quick Win",K6,IF(N7="","",IF(N7="Quick Win",K7,IF(N8="","",IF(N8="Quick Win",K8,IF(N9="","",IF(N9="Quick Win",K9,IF(N10="","",IF(N10="Quick Win",K10,IF(N11="","",IF(N11="Quick Win",K11,IF(N12="","",IF(N12="Quick Win",K12,IF(N13="","",IF(N13="Quick Win",K13,IF(N14="","",IF(N14="Quick Win",K14,IF(N15="","",IF(N15="Quick Win",K15,IF(N16="","",IF(N16="Quick Win",K16,IF(N17="","",IF(N17="Quick Win",K17,IF(N18="","",IF(N18="Quick Win",K18,IF(N19="","",IF(N19="Quick Win",K19,IF(N20="","",IF(N20="Quick Win",K20))))))))))))))))))))))))))))))N6:N20,N25:N26N6=IF(AND(L6="",M6=""),"",(IF(AND(L6="High",M6="High"),"Major Project",IF(AND(L6="High",M6="Low"),"Quick Win",IF(AND(L6="Low",M6="High"),"Thankless Task",IF(AND(L6="Low",M6="Low"),"Fill-In"))))))Cells with Conditional FormattingCellConditionCell FormatStop If TrueG5:H34Expression=AND(setting="Cross out",INDEX($J$6:$O$57,MATCH(G5,$J$6:$J$57,0),4)="DONE")textNOJ6:O34Expression=$O6="DONE"textYESC5:D34Expression=AND(setting="Cross out",INDEX($J$6:$O$57,MATCH(C4,$J$6:$J$57,0),4)="DONE")textNOJ6:O34Expression=$N6="Thankless Task"textNOJ6:O34Expression=$N6="Fill-In"textNOJ6:O34Expression=$N6="Major Project"textNOJ6:O34Expression=$N6="Quick Win"textNOCells with Data ValidationCellAllowCriteriaO25:O26ListCREATED, ASSIGNED, IN PROGRESS, ON HOLD, CANCELLED, DONEO6:O20ListCREATED, ASSIGNED, IN PROGRESS, ON HOLD, CANCELLED, DONEQ5ListCross out, RemoveL6:M20ListHigh,LowL26:M26ListHigh,Low


----------



## DanteAmor (Dec 15, 2022)

These are the formulas, they are *array formulas*. I also attach the file for you to review in detail the array formulas and the conditional formatting.

Dante AmorBCDEFGH4Low EffortHigh EffortLow EffortHigh Effort5High ImpactGOAL 1GOAL 4High ImpactGOAL 1GOAL 46GOAL 3GOAL 5GOAL 3GOAL 57GOAL 13GOAL 7GOAL 13GOAL 78 GOAL 8GOAL 89  10  11  12  13  14  15  16  17  18  19  20Low ImpactGOAL 2GOAL 9Low ImpactGOAL 2GOAL 921GOAL 6GOAL 10GOAL 6GOAL 1022GOAL 11 GOAL 1123GOAL 12 GOAL 1224  25  26  27  28  29  30  31  32  33  34  Hoja8Cell FormulasRangeFormulaC5:C19C5=IFERROR(INDEX($K$6:$K$20,SMALL(IF($N$6:$N$20="Quick Win",ROW($K$6:$K$20)),ROWS($B$5:B5))-ROW($K$6)+1),"")D5:D19D5=IFERROR(INDEX($K$6:$K$20,SMALL(IF($N$6:$N$20="Major Project",ROW($K$6:$K$20)),ROWS($B$5:B5))-ROW($K$6)+1),"")C20:C34C20=IFERROR(INDEX($K$6:$K$20,SMALL(IF($N$6:$N$20="Fill-In",ROW($K$6:$K$20)),ROWS($B$5:B5))-ROW($K$6)+1),"")D20:D34D20=IFERROR(INDEX($K$6:$K$20,SMALL(IF($N$6:$N$20="Thankless Task",ROW($K$6:$K$20)),ROWS($B$5:B5))-ROW($K$6)+1),"")Press CTRL+SHIFT+ENTER to enter array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueC5:H34Expression=VLOOKUP(C5,$K$6:$O$20,5,0)="DONE"textNO

Download the file:
MyFileTest

Example:


----------



## alm395 (Dec 15, 2022)

DanteAmor said:


> These are the formulas, they are *array formulas*. I also attach the file for you to review in detail the array formulas and the conditional formatting.
> 
> Dante AmorBCDEFGH4Low EffortHigh EffortLow EffortHigh Effort5High ImpactGOAL 1GOAL 4High ImpactGOAL 1GOAL 46GOAL 3GOAL 5GOAL 3GOAL 57GOAL 13GOAL 7GOAL 13GOAL 78 GOAL 8GOAL 89  10  11  12  13  14  15  16  17  18  19  20Low ImpactGOAL 2GOAL 9Low ImpactGOAL 2GOAL 921GOAL 6GOAL 10GOAL 6GOAL 1022GOAL 11 GOAL 1123GOAL 12 GOAL 1224  25  26  27  28  29  30  31  32  33  34  Hoja8Cell FormulasRangeFormulaC5:C19C5=IFERROR(INDEX($K$6:$K$20,SMALL(IF($N$6:$N$20="Quick Win",ROW($K$6:$K$20)),ROWS($B$5:B5))-ROW($K$6)+1),"")D5:D19D5=IFERROR(INDEX($K$6:$K$20,SMALL(IF($N$6:$N$20="Major Project",ROW($K$6:$K$20)),ROWS($B$5:B5))-ROW($K$6)+1),"")C20:C34C20=IFERROR(INDEX($K$6:$K$20,SMALL(IF($N$6:$N$20="Fill-In",ROW($K$6:$K$20)),ROWS($B$5:B5))-ROW($K$6)+1),"")D20:D34D20=IFERROR(INDEX($K$6:$K$20,SMALL(IF($N$6:$N$20="Thankless Task",ROW($K$6:$K$20)),ROWS($B$5:B5))-ROW($K$6)+1),"")Press CTRL+SHIFT+ENTER to enter array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueC5:H34Expression=VLOOKUP(C5,$K$6:$O$20,5,0)="DONE"textNO
> 
> ...


Sir,
You. Are. Incredible.

I clearly still have a lot more to learn!

Thank you!!!


----------

