SPILL! Error Merged Cells

%3CLINGO-SUB%20id%3D%22lingo-sub-1939529%22%20slang%3D%22en-US%22%3ESPILL!%20Error%20Merged%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1939529%22%20slang%3D%22en-US%22%3E%3CP%3ETeam%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20SPILL%20error%20on%20my%20spreadsheet%20while%20trying%20to%20equate%20merged%20array%20with%20another%20merged%20array%20from%20another%20sheet.%20Note%20that%20both%20arrays%20are%20same%20location%20same%20size%2C%20but%20when%20I%20I%20try%20to%20do%20the%20calculation%20it%20gives%20SPILL%20error%20due%20to%20merged%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20send%20the%20spreadsheet%20to%20a%20friend%2C%20and%20it%20worked%20fine.%20Could%20that%20be%20something%20from%20my%20version%20or%20the%20settings%20I%20have%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20assist.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1939529%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1939572%22%20slang%3D%22en-US%22%3ERe%3A%20SPILL!%20Error%20Merged%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1939572%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F885337%22%20target%3D%22_blank%22%3E%40Awfimohammed%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20please%20attach%20the%20sample%20file%2C%20which%20will%20give%20us%20better%20idea%20to%20support%20you%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%2FBest%20Answer%20%26amp%3B%20like%20it%20to%20help%20the%20other%20members%20find%20it%20more.%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1939632%22%20slang%3D%22en-US%22%3ERe%3A%20SPILL!%20Error%20Merged%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1939632%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3E%26nbsp%3BPlease%20check%20the%20attached%20on%20original%20post%2C%20just%20added%20an%20example%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1939926%22%20slang%3D%22en-US%22%3ERe%3A%20SPILL!%20Error%20Merged%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1939926%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F885337%22%20target%3D%22_blank%22%3E%40Awfimohammed%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChange%20the%20formula%20to%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3D'Sheet%201'!B4%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1941411%22%20slang%3D%22en-US%22%3ERe%3A%20SPILL!%20Error%20Merged%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1941411%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3BYes%20that%20works.%20How%20did%20it%20work%20on%20your%20side%20if%20you%20use%20same%20formula%20%3D'Sheet%201'!B4%3AD5%2C%20does%20is%20also%20give%20you%20a%20SPILL%20error%3F%20as%20I%20mentioned%20other%20people%20have%20no%20issue%20with%20that%2C%20only%20my%20PC%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1941635%22%20slang%3D%22en-US%22%3ERe%3A%20SPILL!%20Error%20Merged%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1941635%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F885337%22%20target%3D%22_blank%22%3E%40Awfimohammed%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20I%20also%20get%20a%20%23SPILL!%20error.%3C%2FP%3E%3CP%3EPeople%20who%20don't%20get%20a%20%23SPILL!%20error%20are%20probably%20using%20older%20versions%20of%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1944166%22%20slang%3D%22en-US%22%3ERe%3A%20SPILL!%20Error%20Merged%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1944166%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F885337%22%20target%3D%22_blank%22%3E%40Awfimohammed%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eplease%20check%20this%20link%2C%20were%20I%20have%20recently%20explained%20about%20the%20%23SPILL!%20error%2C%20your%20case%20the%20error%20is%20appearing%20because%20the%20cells%20are%20merged.%20What%20you%20can%20do%20here%20in%20this%20scenario%20apply%20%3CSPAN%3Eimplicit%20intersection%20using%20the%20%40%20operator.%20That%20will%20resolve%20your%20issue%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D%40'Sheet%201'!B4%3AD5%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Foffice-365%2Fexcel-spill-error-with-row-multiplication%2Fm-p%2F1937592%22%20target%3D%22_blank%22%3Eexcel%20spill%20error%20with%20row%20multiplication%20-%20Microsoft%20Tech%20Community%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20might%20be%20helpful%20for%20you%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%2FBest%20Answer%20%26amp%3B%20like%20it%20to%20help%20the%20other%20members%20find%20it%20more.%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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,