Forum Discussion
#spill error may be linked to named references and formatting
Excel is issuing #spill! Error messages in one of my worksheets.
Most of the articles I read suggested that this error often originates
From the use of cell merges, which I use extensively. My merged cells
Are all merged vertically. Using “center selected cells” as an alternative
To merging does not work for me as that formatting feature only works
for horizontal ranges, not vertical ranges.
In my case, the error does not appear to be related to merged cells.
In experimenting with this issue, I made two very different discoveries.
- If I use R1C1 references, I do not ever get a #spill! Error. If I use named
References, sometimes I get an error and other times not(unpredictably).
I would very much like to use named references. This suggests to me that the error
has something to do with the way named references refer to the cell value.
Indeed if I evaluate a formula which references to sets of merged cells, I can see
a difference. If I use R1C1 references, just before I execute, I can see integers
for both numerator and denominator and the division executes correctly.
But if I use named references, instead of an integer I get the integer followed by zeros. Ie: integer;0;0;0. Executing with that format induces the #spill! Error.
- If I copy the range of cells that are giving me the error into a new worksheet
In a new workbook, the copied range of cells does not generate an error
even when all references are named references.
Since the values in the selected range are not dependent on any other values
In the entire worksheet or workbook, I expected the calculations to be executed
In exactly the same way, otherwise excel would not scale. This appears to not be
The case. Formatting, which can create a dependency across a range of cells appears
To impact how a cell value is processed. In the old workbook I must have a worksheet wide format set differently than the format in the new workbook.
Any help with either of the above issues will be appreciated.
bil
5 Replies
We do not know anything about your workbook or your formulas.
So, please read this article:
- Thank you for responding.
I don't think you are understanding my dellima
The calculations work with R1C1 references.
The calculations do NOT work with Named References.
The error, #spill!, occurs only when Named References are used when at least one of the cell values is referenced by names. The error seems unpredictable.
The article you suggested I read does not appear to even hint of the above
condition as being a cause of the error.
The subtypes of #spill! error ARE indicated.
1) "A cell we need to spill data into is not blank."...which is not my case.
2) "We can't spill into a merged cell". That IS the case but only if I use named references. for the exact same merged cells, i don't get the error, "we can't spill into a merged cell" if i use R1C1 referencing.
That is why I posted the question. This issue does not appear to be addressed anywhere.
It would be nice ifi this forum allowed one at attach a worksheet or segment of a worksheet.
