Results 1 to 6 of 6
  1. #1
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Data validation annoyance Excel 2010

    In my spreadsheet I have a number of columns where data types are selected from a series of drop down list of options. Most drop down lists start the display with the data item that is in the first cell in that specified range.
    However one list, instead of starting with the first item in the specified range, displays the last cell in that specified range, can't figure out how to change this behaviour
    Paul Coyle
    Approach love and cooking with reckless abandon

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Do you mean it displays that item at the top of the list, or it defaults to that item (but in the correct place)? If the latter, that would happen if that were the current selection in the cell.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts
    Hi Rory. This particular list, which is sorted alpha A - Z when I click the display list button, it defaults to starting the list display at the blank cell under the last item on the list, instead of displaying the first item on the list as do the other columns - see column G in attached sample
    Attached Files Attached Files
    Last edited by pccoyle; 2012-09-10 at 15:41. Reason: Attach File
    Paul Coyle
    Approach love and cooking with reckless abandon

  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
    If no match from the list is found the data-validation defaults to no selection in the list. If there is a matching cell, it defaults to the first one found.

    The problem is that the your range (=$AE$3:$AE$60) contains blank cells at the end. Thus the empty cells of column G defaults to the 1st matching cell which is the fist blank cell (the cell past the last item in your list). Change your range to (=$AE$3:$AE$58) and you will not have that problem.

    Steve

  5. #5
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts
    Thanks Steve, left room to add extra items while I test this project, so will adjust ranage to fit. Cheers
    Paul Coyle
    Approach love and cooking with reckless abandon

  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
    If the ranges can change, you can make them dynamic. For example create a name (eg called "CallType")that refers to:
    =OFFSET(THL!$AE$3,0,0,COUNTA(THL!$AE:$AE)-1,1)

    Then in datavalidation you can use for the list:
    =CallType

    And as items are added or removed the range will expand and contract

    Steve

  7. The Following User Says Thank You to sdckapr For This Useful Post:

    bethel95 (2012-09-13)

Posting Permissions

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