Spill Error when referencing cell with data validation

%3CLINGO-SUB%20id%3D%22lingo-sub-1479577%22%20slang%3D%22en-US%22%3ESpill%20Error%20when%20referencing%20cell%20with%20data%20validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479577%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I'm%20building%20a%20spreadsheet%20to%20allow%20me%20to%20track%20progress%20of%20tasks%20for%20work.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20designed%20a%20%22display%20page%22%20with%20a%20merged%20cell%20and%20I%20want%20to%20display%20the%20information%20within%20a%20cell%20of%20a%20different%20page%20that%20contains%20a%20drop%20down%20list.%20I%20used%20%3D%22cell%20reference%22%20but%20then%20got%20a%20spill%20error%2C%20I%20then%20tried%20the%20%3Difs%20function%20to%20display%20the%20text%20but%20still%20im%20getting%20a%20spill%20error.%26nbsp%3B%3C%2FP%3E%3CP%3Ewhen%20I%20unmerge%20the%20cells%20on%20the%20%22display%20page%22%20I%20get%20this%20but%20cant%20work%20out%20what%20im%20doing%20wrong.%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20276px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F199934i372515DE3172BCD1%2Fimage-dimensions%2F276x111%3Fv%3D1.0%22%20width%3D%22276%22%20height%3D%22111%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1479577%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479624%22%20slang%3D%22en-US%22%3ERe%3A%20Spill%20Error%20when%20referencing%20cell%20with%20data%20validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479624%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F706034%22%20target%3D%22_blank%22%3E%40Bmarriott%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExcel%20considers%20all%20but%20first%20cells%20which%20were%20merged%20as%20occupied.%20Your%20reference%20or%20formula%20returns%20an%20array%20from%203%20elements.%20If%20you%20return%20it%20into%20merged%20cells%2C%20actually%20you%20have%20only%20one%20cell%2C%20other%20two%20are%20%22busy%22%2C%20thus%20%23SPILL!%20error.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20that's%20not%20a%20good%20idea%20to%20use%20merged%20cells%20in%20Excel%2C%20there%20are%20a%20lot%20of%20side%20effects%20like%20this.%20In%20most%20cases%20Center%20across%20selection%20is%20a%20good%20alternative.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThus%20the%20reference%20returns%203%20elements%2C%20not%20one%2C%20is%20unclear%20from%20your%20description.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479633%22%20slang%3D%22en-US%22%3ERe%3A%20Spill%20Error%20when%20referencing%20cell%20with%20data%20validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479633%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EThanks%20for%20the%20reply%2C%20I'll%20go%20down%20the%20route%20of%20just%20resizing%20the%20cells%20then.%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

So I'm building a spreadsheet to allow me to track progress of tasks for work. 

I have designed a "display page" with a merged cell and I want to display the information within a cell of a different page that contains a drop down list. I used ="cell reference" but then got a spill error, I then tried the =ifs function to display the text but still im getting a spill error. 

when I unmerge the cells on the "display page" I get this but cant work out what im doing wrong.

 
 

image.png

 

 

2 Replies

@Bmarriott 

Excel considers all but first cells which were merged as occupied. Your reference or formula returns an array from 3 elements. If you return it into merged cells, actually you have only one cell, other two are "busy", thus #SPILL! error.

 

In general that's not a good idea to use merged cells in Excel, there are a lot of side effects like this. In most cases Center across selection is a good alternative.

 

Thus the reference returns 3 elements, not one, is unclear from your description.

@Sergei BaklanThanks for the reply, I'll go down the route of just resizing the cells then. 

Cheers