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
FALSE) so you must
NOT() one of them. Whichever way you go, you will have a result you can use in a simple
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).
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
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:
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:
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.