Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    pull-down choices ('97)

    What is the best way to create a pull-down list in a cell?
    I want the user to pick from a limited number of choices...
    For instance, 50%, 60%, 70%, 80%, 90%, 100%...

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: pull-down choices ('97)

    Data | Validation, using the option for a List of allowed entries. Once you start setting Data Validation, the dialog choices are pretty self-evident, but post back if you want further help.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: pull-down choices ('97)

    I use Data Validation a lot to restrict data entry to valid choices.
    It's an excellant method and doesn't involve programming.
    Unfortunately, with Data Validation alone a User can simply bypass your 'restricted' choices by "cutting and pasting" into the cell.
    My solution is to use a named range as the source for the dropdown choices, and then use a 'hidden' cell which has a 'MATCH' formula to check whether the actual cell entry is in the specified named range or not. I then use this cell in conjunction wih a conditional format to set the data entry cell with a red backgtound if the contents aren't in the allowed values. Does this make sense to you?

    zeddy

  4. #4
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pull-down choices ('97)

    Perfectly.
    Its also useful on multisheet worksheets to have the Summary sheet conditionally format based on any errors in other sheets.
    The format I use for the summary sheet is to change all characters to strikeout on a grey background - impossible to work with without knowing something is amiss.
    While conditional formating does not work across sheets, the summary sheet can have a hidden cell that says something like =OtherSheet!A1 to handle that.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pull-down choices ('97)

    I've tried data validation and it does just what I want...
    It seems that it wants the "list" to be situated on the same sheet?

  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: pull-down choices ('97)

    If you have a list on another sheet and name it (insert -name -define)

    you can use the name of a range from another sheet.

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pull-down choices ('97)

    Excellent, thanks!

  8. #8
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pull-down choices ('97)

    What you describe sounds like a good approach. I think I follow it conceptually...

    <<My solution is to use a named range as the source for the dropdown choices, and then use a 'hidden' cell which has a 'MATCH' formula to check whether the actual cell entry is in the specified named range or not.>>

    Could you give some more details about the hidden cell and the match formula?

    <<I then use this cell in conjunction wih a conditional format to set the data entry cell with a red backgound if the contents aren't in the allowed values>>

    This one, too?

    Thanks!

  9. #9
    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: pull-down choices ('97)

    Attached is an example.

    On the "List Page" sheet is a range of items in A1:A8. The range is named "ItemList"

    In the "Validation Page" sheet in cell A1 has data validation (list, source: =ItemList)
    In B1 has the formula:
    <pre>=IF(ISNUMBER(MATCH(A1,ItemList,0)),"","BAD Value")</pre>


    It has conditional formatting to be white text with red bkgrd if the cell value is "not equal to" =""

    So if use the datavalidation pull down, the item is always from the list and B1 stays null. If you put (for example) an "a" in cell A2 and then move or copy A2 to A1 your validation is gone (copied over) and the "a" is not in the list to B1 shows "BAD Value"

    Steve

  10. #10
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pull-down choices ('97)

    Thanks for the details...
    was there supposed to be an attached file?

  11. #11
    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: pull-down choices ('97)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> here it is. I forgot it gets unattached in "preview mode" and you go automatically to preview mode with the <!t>[pre]<!/t> tags

    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
  •