Excel Decision Matrix

How would I go about make the following situation possible on my excel sheet.



I have different tests that need to be ran and am trying to create a decision matrix to sort their importance. One of the factors is parts available, this is an all of nothing criteria, if the parts aren't available the test cannot be ran. Therefore, I need to find a way so that if a check box is marked as the parts are available or not available then those tasks are automatically dropped below the tasks in the priority ranking that do that parts available. The tricky thing is after they are dropped below those tasks they need to still be ranked according to the other criteria.





Is this possible in excel?



Thanks, hope that makes sense

Attached is a high level file of what I hope the file will be able to do. Notice how when the Box is checked a total score is given but if left unchecked it is defaulted to 0. It would be nice if the projects were sorted solely based on parts available then in those containers sorted based on total points.

(other criteria besides RPN will be added so that is why the weighting is in there)

Why not just add a column at the end and rank everything and then filter the rank column?

Lose the boxes, stick with Y/N and numbers abnd you can use IF & RANK to do this for you

Just add a box in the sheet explaining how to priorise things so the right values are assigned to the right boxes (maybe use conditional drop down boxes to ensure consistent usage?)