#spill error may be linked to named references and formatting

Occasional Contributor

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.

 

  1. 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.

 

 

 

  1. 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
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.

@WHBurling 

However, it's better to have sample or more concrete description which allows to reproduce an error. In general named references are using quite often, so far I didn't see any errors related to that.

@WHBurling 

If you open the full text editor you see a field to which you can drag a file.

 

TechCommunity Upload.PNG

Thank you Detlef!
I tried dragging my file into the full text editor. The editor refused to accept the file type of XLSX. I did copy the cells from the worksheet but the copied result does not allow you to see the formulas so YOU guys can test out the formulas. It appears to be just a static snapshot of my worksheet range of interest