Threat Matrix Tiering System 2.0

[cobraspeed]

Hello everyone - Newdoverman (registered expert) assisted me in creating the following book. The problem I found is that I have no clue how to extend beyond the 20 gangs he has added. I wrote to him in another post and this is what I had mentioned. I am in a crunch and need this by lunch today if possible. Can someone help?

"Newdoverman, I am attempting to add additional gangs to the sheet and I am lost. I have tried to extend cell 319 to well beyond that, but I am unable to get the formulas to apply. For example, attempting to add gang 21, I copied the formula from cell B2 and applied it to V2 - V16. I copied the sum formula as well. I'm lost at this point because it is not pulling from below. If I select F2 on any cell above to view the formula, the value within the cell disappears."


His original post to me is below:

1. Here is one way of doing this. I created a listing down column A with the Gangs listed once for each question. The question numbers were entered beside the Gang number in column B.

A Matrix of Questions going across and answers listed down was formed with a score attached to each question and answer. This was then used in column C to determine the score for the question for each gang.

The score for each question was extracted from the Question/Answer matrix with this formula entered in C20 and filled down.
=INDEX($G$21:$U$30,MATCH(C20,$F$21:$F$30,0),MATCH(B20,$G$20:$U$20,0))

The results of this question/answer combination was transferred to your original matrix with the following formula entered in B2 and filled down and across. This is an array formula to be entered with Ctrl + Shift + Enter.
Formula:
=IFERROR(INDEX($D$20:$D$319,SMALL(IF($A$20:$A$319=B$1,ROW($A$20:$A$319)-MIN(ROW($A$20:$A$319))+1),ROWS($1:1))),"")


Each column was then totalled with this formula entered in B17 and filled across
Formula:
=SUM(B2:B16)


The top 1/3 are identified by Conditional Formatting to be RED, Middle 1/3 formatted ORANGE, bottom 1/3 not formatted.

[BadlySpelledBuoy]

How many gangs do you need to add?

BSB

[BadlySpelledBuoy]

1) Add your additional gangs at the bottom of the list and add their answers
2) Add your additional gangs to the right of the list in row 1
3) Drag the score formula down
4) Amend the formula in B2 to account for these additional gangs - IMPORTANT: Remember to confirm the formula with Ctrl+Shft+Ent as it's an array formula
5) Copy that formula down to B16
6) Highlight B2:B16 and copy those formulas across as necessary

BSB

[cobraspeed]

How many gangs do you need to add?

BSB

Hello Sir, there is no real answer for that as the number could grow exponentially over time. I was thinking 100 to start but as new ones are identified I would like to be able to add them with ease.

[BadlySpelledBuoy]

Follow the steps in post #3 and it should be easy enough.

BSB