#spill error, but I want to spill anyway

[smking204]

column A has expected values, column B has actual values, column C organizes A in a certain way, and column D uses a repeated formula to organize B in relation to C. Here is the essence of the formula in D:

if(c3<> "", useMatchFormula, if(and(c3= "", c2<> ""), "", if(and(c3= "", c2= "", c1<> ""), useUniqueFormula, "")))

this formula runs for 500 cells, allowing the list to populate correctly irrespective of the lengths of A, B, or C. The only problem, of course, is that once the unique formula triggers, it's trying to spill into cells that have a formula in them. The obstructing formulas are set to "", but I still get the #spill error.

Is there a way to tell the formula to spill anyway, overriding the existing formulas? I'm not sure of any suitable workaround. Any help is appreciated, thanks in advance!

 

[Fluff]

Is there a way to tell the formula to spill anyway, overriding the existing formulas?

Nope, anything in the way of a spill range will stop it from spilling.

 

[Jon Peltier]

You need to put the dynamic array formula someplace where there will not be anything impeding its ability to spill.