#SPILL! From a Referenced Merged Cell.
Hi everyone, I have a spreadsheet that I'm trying to build for my work flow to enable track my daily sales activities and simultaneously compute my percentage earnings per customer. For me to achieve...
Forum Discussion
Hi Sergei Baklan, I'm having a problem too in understanding the spill function.
When i entered a date in a cell, but i merged with with other two cell due to the small column I created. But when i want to link that date into other cell, it appears as it is in one cell, but it appears "0" in the next two cell next to it, and I cant delete that 0. Can u help?
When i entered a date in a cell, but i merged with with other two cell due to the small column I created. But when i want to link that date into other cell, it appears as it is in one cell, but it appears "0" in the next two cell next to it, and I cant delete that 0. Can u help?
It depends on the nature of the link.
=A1:C1 will give the date followed by 0s to fill the array to 3 values
=A1 will just give the date
The manual process of selecting cells is somewhat messy in the presence of merged cells and the normal advice is to avoid their use where possible.
=A1:C1 will give the date followed by 0s to fill the array to 3 values
=A1 will just give the date
The manual process of selecting cells is somewhat messy in the presence of merged cells and the normal advice is to avoid their use where possible.
- I am working on a project schedule where I need to use merged cells to display dates in an array. I am using dynamic array formular but whenever I enter the formula in the first merged cells which is supposed to spill into the next merged cells I get the #SPILL error! Any help for me:
The formula is as follows and I want it to spill into the other cells for the schedule computation and to make the dates dynamic for Plan vs Base. I am using Office 2016:
=IF(display="PlanvsBase",IF(timeline.base.start>0,MIN(timeline.base.start,timeline.plan.start),timeline.plan.start),timeline.plan.start). The formula is based on defined namesI am happy with your use of defined names but there is nothing to suggest that any one of them represents an array. If they are indeed scalars, your formula could reduce to
= IF( AND(display="PlanvsBase", timeline.base.start>0), MIN(timeline.base.start,timeline.plan.start) + {0,1,2}, timeline.plan.start + {0,1,2} )where I have added the + {1,2,3} simply to generate an array of consecutive days.
That said, I have some problems understanding the scenario you outline.
You mention Office 2016. That version of Excel does not support dynamic arrays at all.
The next problem is that any attempt to use a spilt range within a merged cell will generate a #SPILL! error. The error disappears if one unmerges to cell. These last behaviours are a characteristic of Excel 365 (and Excel 2021) but not earlier versions.
BTW. It is probably better to start new discussions for supplementary questions rather than tagging on to a pre-existing discussion.
