Blank Rows from Merged Cells in Drop Down Menu

[Kati]

It is probably easier to explain how to recreate my problem...

Merge a cell to the cell below it. Now merge the cell below the merged
cell to the cell below it. Make one more merged cell. Now put text in
each one like this "excel", "hates", "me".Now click on a different cell
somewhere else on the page and go to menu Data --> Validation. Choose
"List" and then in the source click the little button to the right and
then highlight your three cells. Now you have a drop down menu in the
somewhere else cell. But when you go to the dropdown menu it has blank
spaces in between the entries (because of the merged rows). If you do
the same thing but the list is with cells that are not merged to the
rows below then it doesn't have the blank rows. If you are only dealing
with cells that are merged in twos it is okay because it is spaced all
pretty and everything. The problem is when the cells are not merged
evenly so everything looks uneven.

So any ideas on how to get rid of the apparent blank entries in the
drop down menu??

-Kati

 

[Arvi Laanemets]

Hi

Simply don't use merged cells as source for data validation lists. When you
merge cells, all values in merged range ,except the one in loftmost upper
cell, are cleared. At same time, all merged cells exist on worksheet -
merging only afects what is displayed.

Create your list elsewhere, and use this list as data validation list
source. When you need this list to be displayed in range with merged cells,
use there formulas to get those values there.


Arvi Laanemets