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
