I'm trying to achieve nested conditions in Google Sheets. 3 columns test to see if a condition exists (find the word "nulled" across other columns). They're called into action by their number (1,2,3). If the column is already handling a conditional level, then it does not perform the test but copies the cell above to continue the decision (using INDIRECT). They're summarised in the final column, again, looking across the row to see if annulled or not.
I have a working formula but it won't sit within ARRAYFORMULA because it uses INDIRECT to reference the cell content above. I desperately want to put it into ARRAYFORMULA as the current formula has to be copied to every cell.
=
if ($E: $E < $1: $1, "<",
if ($E: $E > $1: $1, indirect("R[-1]C[0]", false),
if (and($E: $E = $1: $1, isblank($A: $D)), indirect("R[-1]C[0]", false),
if (and($E: $E = $1: $1, not(isblank($A: $D))),
if (or($A: $A = "nulled", $B: $B = "nulled", $C: $C = "nulled", $D: $D = "nulled"), "nulled", "active"),
"-"))))INDIRECT doesn't sit within ARRAYFORMULA so is there an alternative way to achieve it? (or any other feedback on how to do nested conditionals a better way...)
