nicksinthemi
03-14-2013, 07:53 AM
Hi all
Really interesting challenge here and any help even on where to start would be much appreciated.
So I have attached my spreadsheet. It's a dependency matrix that I invented and I'm trying to scale it up. I'm quite proud of it, but it is no longer fit for purpose.
This is the idea - there are activities which may have several dependencies. I want to translate the dependency matrix view to the week view telling me when the activity is 'ready'. 'Ready' is defined by the date at which the latest dependency exceeds a 3.
A data input sheet allows me to rate the maturity of these dependencies on a week by week basis. As soon as all dependencies reach a maturity level 3, that activity goes green on the BLOCK PLANNING sheet.
I used a bit of clever sheet copying and come conditional formatting to layer this up into several colours depending on the 'maturity level'.
My current solution is anything but elegant. I counted the rows for each activity set this up in an if clause to produce a binary result on the basis of a less than comparison. What does it compare against? Well, I basically multiply two IF conditions together for every cell in the DEPENDENCY MATRIX row. So if true it produces a 1x1 and then I add together these instances for the entire row. For that 1x1 the formula asks 'is the cell filled' on the one hand, and 'is the dependency for this week a 3 or above' on the other.
Finally, using excels $ logic I had my solution by dragging across. Beautiful. Actually pretty proud of this being a novice.
So now the challenge - expanding the columns to 100+
Problems: uses more than 64 nested ifs; the formula is also too long if I try to 'outsource' the if clauses to single cells of other sheets (though I had it there).
Question: is there a more elegant way of doing this at a larger scale in excel? Is VBA necessary or is there a better formulation?
Really interesting challenge here and any help even on where to start would be much appreciated.
So I have attached my spreadsheet. It's a dependency matrix that I invented and I'm trying to scale it up. I'm quite proud of it, but it is no longer fit for purpose.
This is the idea - there are activities which may have several dependencies. I want to translate the dependency matrix view to the week view telling me when the activity is 'ready'. 'Ready' is defined by the date at which the latest dependency exceeds a 3.
A data input sheet allows me to rate the maturity of these dependencies on a week by week basis. As soon as all dependencies reach a maturity level 3, that activity goes green on the BLOCK PLANNING sheet.
I used a bit of clever sheet copying and come conditional formatting to layer this up into several colours depending on the 'maturity level'.
My current solution is anything but elegant. I counted the rows for each activity set this up in an if clause to produce a binary result on the basis of a less than comparison. What does it compare against? Well, I basically multiply two IF conditions together for every cell in the DEPENDENCY MATRIX row. So if true it produces a 1x1 and then I add together these instances for the entire row. For that 1x1 the formula asks 'is the cell filled' on the one hand, and 'is the dependency for this week a 3 or above' on the other.
Finally, using excels $ logic I had my solution by dragging across. Beautiful. Actually pretty proud of this being a novice.
So now the challenge - expanding the columns to 100+
Problems: uses more than 64 nested ifs; the formula is also too long if I try to 'outsource' the if clauses to single cells of other sheets (though I had it there).
Question: is there a more elegant way of doing this at a larger scale in excel? Is VBA necessary or is there a better formulation?