Is it possible to detect if a cell has been spilled into?

I like putting a conditional formatting on my sheets which is simply =ISFORMULA(A1) and encompasses the entire sheet.

So, basically, if a cell contains a formula, it gets a grey background or something of the sort, which makes for a clear distinction between cells which expect user input (not grey) and those which are output and shouldn't be messed with (grey).

However, when using a spilled array result, only the cell which actually contains the formula gets marked by this formatting, with the spilled results remaining unaffected by the conditional formatting.

So, is there some ISFORMULA() equivalent or some other way of detecting whether a cell has been spilled into?

4 Answers

Not entirely sure if this helps, but you can refer to the list of spilled results (within formulas) when you don't know how many results there are by using the spill range operator #. For instance, if a result at A1 spills into A2,A3, etc, and you use A1# it will pull the whole range or results automatically. You can also use counta(A1#) to quantify how many results there are, perhaps as part of a check function to see if there are any spills like if(counta(A1#)>1,true).

An efficient solution is to define the following simple VBA function:

Function isSpill(x As Range) isSpill = x.HasSpill
End Function

Then calling something like =isSpill(Z100) returns TRUE for cells which have been spilled into by some other cell.

For my conditional formatting situation, this can then be trivially combined with =OR(ISFORMULA(A1), isSpill(A1)) to also include non-spill formulas.

However, this has the obvious (and pretty big) downside that it requires an .xlsm file and for users to always have to enable macros. So a non-VBA solution would be welcome, if possible.

Say we know that A1 has a formula that spills down and we want to list the cells that are occupied by the spill. Try:

=TRANSPOSE(ADDRESS(SEQUENCE(1,COUNTA(A:A)),1))

(here we use a Spill to describe a Spill)

and to put the answer in a single cell:

=TEXTJOIN(", ",TRUE,TRANSPOSE(ADDRESS(SEQUENCE(1,COUNTA(A:A)),1)))

EDIT#1:

To determine if a cell is filled by entry or by Spill, just click on it and run:

Sub DontCryOverSpiltCell() MsgBox (ActiveCell.Address & vbCrLf & ActiveCell.HasSpill)
End Sub

You can use the Excel 4 Macro function, IN a Named Range, to test whether a cell has a formula via GET.CELL(41,"cell being checked") which WILL work on the formula a SPILLED Into cell has. It will ALSO return the contents of non-formula cells, so a further test needs included, via AND() being the simplest approach, of, say, Does the result of whatever is in the cell equal the result of the GET.CELL() result?

If it does, then the content is PROBABLY a constant of some sort, numerical or string. However, a formula will likely return a value utterly different than the text of the formula itself, like... essentially always. So if the test fails, one would assume, with good chances, a formula in the cell. That's half the battle.

Then, is it a SPILL formula? Well, usually, one does not encounter contiguous cells, either up/down or right/left (that's right, unless you can eliminate right/left SPILL you need to test for both here so hopefully one can eliminate one or the other as a concern). So A1-A2-A3-A4-A5, say, would maybe have related formulas, but not IDENTICAL formulas, as a SPILL range would. Testing the content of a cell, say, three or 14 cells up (as one's guess about such might suggest: the more distant a match, the likelier SPILL is at work, but also the greater the chance that simply something odd is occurring) vs. the cell of interest ought to be a decent test. Do that via GET.CELL() as well.

Binding the two tests together (AND() again) in a single Named Range would give you a return that is problematic since you need AND() here, but the two tests are going to give opposite results (of TRUE and FALSE) so you must NOT() one of them. Whichever way you go, you will have a result you can use in a simple IF().

Unfortunately, FORMULATEXT() returns "" for a SPILLED Into cell's contents. That'd be the way to go otherwise (sigh...).

There are other ways to do the testing, like TYPE() for instance instead of does the GET.CELL() result equal a simple =cell's contents result. But nothing in the approach is direct in the sense of "exact and unambivalent" so one needs to fit the testing to the situation. The method described above works, and it looks to me like a pretty decent test (the critical part being the assumption that contiguous cells should have no closer than "similar" formulas, not "identical" ones).

EDIT

No teaching necessary as you'll write the formula and they'll use the results, not the technique:

It's part of the macro language from then, but it is actually simply a function, like SUM() or VLOOKUP() with one single exception: it will not work if entered DIRECTLY into a cell. You write a perfectly normal formula, say to check cell A5, just like this:

=GET.CELL(41,$A$5)

but you first create a Named Range, call it Horse perhaps, then write it there (or write it cell-side where it won't even be treated as a function, but who cares, right, you just want the text, and copy it to the clipboard, then make the Named Range and just paste it in). So now you have a Named Range called Horse and the "Refers to" box has the above in it.

Back in the cells, let's say you want the result in cell F23. In that cell you just type:

=Horse

and it finds the value by using the function for you. ALL you and the user see in the cell is the result.

No macro at all, just a perfectly normal formula with a weird name. Give it a normal one and no one's even wondering anything.

So, before giving up on it, just try the above: make some Named Range, maybe called Experiment, put that formula in, make some spill function that puts some value into cell A5, then pick some other cell and type =Experiment and see the formula the content of A5 spawns from.

If you still feel weirded out, that's cool. But maybe you'll see how easy it is and like it. Just would take 2-3 minutes.