Forum Discussion
ituryu
Jan 22, 2020Brass Contributor
#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...
SergeiBaklan
Jan 22, 2020MVP
That means you are on dynamic array version of Excel and your formula returns an array for which not enough space. 2019 returns only first element of such array. Better to rework the formula, but you may try to put in front of it @ sign. For example, if your formula
=INDEX(...)
convert it to
=@INDEX(...)
Carrey1608
Apr 22, 2021Copper Contributor
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?
- PeterBartholomew1Apr 22, 2021Silver ContributorIt 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.- Victor_ManuOct 07, 2022Copper ContributorI 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 names- PeterBartholomew1Oct 08, 2022Silver Contributor
I 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.