Let’s say we have a horizontal list of data, a simple a,b,c,d,e,f will suffice, one letter per cell in the range A1:F1. Now add a Data > Validity drop-down in A2 (or anywhere really) referencing A1:F1 as a cell range. Everything looks great. Now do a merge of cells A1 and B1. You’ll get the error about whether to keep or discard the data in the ‘hidden’ cells, in this case B1. My list then appears to be a,c,d,e,f.
If you choose the keep the hidden cell data that hidden ‘b’ will still be in the drop-down list. If you choose to delete the hidden contents then there’s this mystery blank in my drop-down list. It seems duplicate blanks are automatically removed so doing multiple merges with deleting the hidden contents does not produce multiple blanks in the drop-down. This behavior is the same regardless of the ‘allow empty cells’ option before anyone suggests that (that’s not what that does just for reference). Interestingly enough duplicate data that isn’t blank will appear twice or more but blanks only show once per list.
How do I get my drop-down to display exactly and only the same data set that I see when actually looking as cells A1:F1 and effectively ignore these hidden cells? I can do a sort to put the blank at the top but in many cases I don’t want the remaining data sorted alphabetically so while this cleans things up a little it’s not a workable solution in some situations.