Search code examples
excelexcel-formula

Override spill cell

Is there a way to override spill cells without getting an error? Lets say in the A column I the cells contain some numbers in B1 I have "=A1:A10". Now I want to have a different value that I set only in B7. How can I override the value of this cell without getting a spill error?

Example:

Sample issue

Solution

  • A SpillRange will only spill if the entire range it's expanding to is empty -- no way around this. However, there are lots of ways to accomplish the same functional result by adjusting the formula to behave differently in that particular cell.

    Here's a few ideas:

    =IF(ROW(A1:A10)=7,"Something Else",A1:A10)`

    I prefer to use the LET function to reduce the number of address references in a formula, even if it makes it longer, so I'd use:

    =LET(zRange,A1:A10,IF(ROW(zRange)=7,"Let version Something Else",zRange))`

    Overall something like Map Function seems most useful. I like including helper arrays as this:

    =LET(keyRng, A1:A10, iRng, SEQUENCE(ROWS(keyRng),1),gResult,
    MAP(keyRng,iRng, LAMBDA(kVal,iVal,IF(iVal=7,"oh NO!!", iVal))),gResult)

    enter image description here