+ Reply to Thread
Results 1 to 3 of 3

Blank Rows from Merged Cells in Drop Down Menu

  1. #1
    Kati
    Guest

    Blank Rows from Merged Cells in Drop Down Menu

    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


  2. #2
    Arvi Laanemets
    Guest

    Re: Blank Rows from Merged Cells in Drop Down Menu

    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


    "Kati" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




  3. #3
    Registered User
    Join Date
    11-09-2011
    Location
    Greece
    MS-Off Ver
    Excel 2010-2013
    Posts
    2

    Re: Blank Rows from Merged Cells in Drop Down Menu

    I had the same blank rows into the drop down list (created from data validation).
    I understand that this was caused due to upgrating from 2003 to 2010.
    I done the below trick and solved it:
    The 2003 file was opened with 2007 excel.
    I convert the file to 2007 (from office button/ convert).
    Then it is ok to open with 2010.

    further to the above, i believe it is the responsibility of microsoft to keep integrity of excel to next versions.
    Im still looking for a better solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1