Forum Discussion
Spill Error when referencing cell with data validation
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.
2 Replies
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.
SergeiBaklanThanks for the reply, I'll go down the route of just resizing the cells then.
Cheers
