SPILL! Error Merged Cells

Copper Contributor

Team,

 

I have a SPILL error on my spreadsheet while trying to equate merged array with another merged array from another sheet. Note that both arrays are same location same size, but when I I try to do the calculation it gives SPILL error due to merged cells.

 

I send the spreadsheet to a friend, and it worked fine. Could that be something from my version or the settings I have?

 

Please assist.

7 Replies

Hi @Awfimohammed 

 

Can you please attach the sample file, which will give us better idea to support you 

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.

 

@Faraz Shaikh Please check the attached on original post, just added an example 

@Awfimohammed 

Change the formula to:

='Sheet 1'!B4

@Detlef Lewin Yes that works. How did it work on your side if you use same formula ='Sheet 1'!B4:D5, does is also give you a SPILL error? as I mentioned other people have no issue with that, only my PC?

@Awfimohammed 

Yes, I also get a #SPILL! error.

People who don't get a #SPILL! error are probably using older versions of Excel.

 

Hi @Awfimohammed,

 

please check this link, were I have recently explained about the #SPILL! error, your case the error is appearing because the cells are merged. What you can do here in this scenario apply implicit intersection using the @ operator. That will resolve your issue 

 

=@'Sheet 1'!B4:D5

 

excel spill error with row multiplication - Microsoft Tech Community

 

Hope this might be helpful for you 

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.

@Faraz Shaikh Thank you.

 

It seems Excel added a new feature that caused this error on merged cells. Thanks for the tip, it works.

 

My problem is that I want to distribute my workbook to many employees and I tried to find an easier way for them to make that calculation without getting SPILL! I finally used one cell instead of a merged to be safe. 

 

Regards,