Spill Error when referencing cell with data validation

Copper 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