Results 1 to 6 of 6
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Data Validation list dropdown (XL2000)

    Two Questions..
    1. Is there any way to increase the 8-entries available in a drop-down data validation list?

    2. What 'controls' the width of the validation list dropdown?
    I have 2 sheets with data validation assigned to 20cells in a column. The same data-validation list source is used on both sheets (list source is a named range). Both columns have the same width.
    On one sheet the dropdown list is about the width of the column containing the validation.
    But on the other sheet, the dropdown list width is 'expanded' over about 4 columns to the left.
    What gives?????

    zeddy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Validation list dropdown (XL2000)

    The number of entries in the dropdown list can be very large, but only 8 items are visible at once. There is no way to change this; the validation dropdown list is not configurable.
    The dropdown list of Data Validation is more or less as wide as the cell. If you apply it to merged cells, the list will be as wide as the merged cells together, i.e. several columns wide.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: Data Validation list dropdown (XL2000)

    Hi Hans
    Thanks for the info re non-configurable dropdowns.

    I'm still puzzled about the width thing - your comment on merged cells is noted.
    I have attached a small sample file to hopefully demonstrate the 'glitch'.
    On sheet [test], cell [d5] the dropdown extends left across columns B, C and partially A.
    On sheet [parameters], cell [d5] the dropdown behaves as expected.
    The data validation list source is same for both.
    I loaded the file into ExcelXP with same result.
    I can't see what is going on - i.e. why the dropdown widths are different.


    zeddy

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Data Validation list dropdown (XL2000)

    This doesn't answer your question, but if you want to ged rid of the quirkiness in TEST copy its contents to a different sheet.

    The "quirkiness" seems to be something with the TEST sheet. Cells with validation copied to parameter sheet or a newly created sheet are FINE. Cells with data validation copied from other sheets to TEST become very wide. If you copy the ENTIRE worksheet test, the NEW copy has this same quirkiness.

    BUT, If you create a NEW sheet and select ALL of TEST sheet (ctrl-a) and paste into the new sheet, it loses the quirkiness.

    I speculate it is some sheet setting (which I have no idea where it is?) or a "benign" corruption in the sheet test. Did the TEST sheet come from another source (imported from Lotus, a text file or something like that) and this is a "glitch" in the conversion? I have seen that some functions do not work correctly in sheets converted from Lotus files and I make a leap of behaviour problems in conversion.

    Steve

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: Data Validation list dropdown (XL2000)

    Many thanks Steve.
    I tried the copy-all-to-a-new-sheet trick and it 'cleared' the extra-wide dropdown.

    The original source was Excel. I don't know how the original User managed it.
    From what Hans said I suspect there were originally some merged cells somwhere which were then removed.
    I would be interested in finding out how to 'create' these extra-wide dropdowns.
    I can see some use for having them. For example, for space reasons a column width may be reduced but you would still like to select an enty from an extra-wide dropdown. Long entries are usually truncated to the width of the column containing the validation dropdown.

    zeddy

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Data Validation list dropdown (XL2000)

    I can see the use, but controlling might be an issue. ALL of the data validation pulldowns on that sheet became wide NOT just those in that column. Newly created ones also.

    Steve

Posting Permissions

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