Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autoselecting values (2000)

    Is is possible to include a list of some sort where l can choose from a selection.
    i.e in the screenshot l keep having to type in the date and version number all the type as l do this on a regular basis it is very repetitive.
    I would like to choose the version number and date rather than keep having to type them in all the time.
    I already have a drop down which is an autofilter, this needs to be on at all times.

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

    Re: Autoselecting values (2000)

    You can use Data | Validation with the List option.

  3. #3
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autoselecting values (2000)

    I am not to sure how you use this option please can you explain in a bit more detail, thanks.

  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: Autoselecting values (2000)

    Check out MS MVP Debra Dagliesh's Data Validation - Basics

    Steve

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

    Re: Autoselecting values (2000)

    Start by creating lists of allowed values: on another part of the worksheet, type a list of version numbers, and a list of dates.

    Next, select the cells in which you want to enter a version number.
    Select Data | Validation.
    Select List from the Allow dropdown.
    Click in the Source box.
    Point to the list of version numbers.
    Activate the Input Message tab.
    If you wish, enter a message that will be displayed when you select one of the cells.
    Activate the Error Alert tab.
    If you want to be able to select ONLY values from the list, set the Style to Stop.
    Enter an appropriate error message.
    Click OK.

    Repeat for the cells in which you want to enter dates.

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autoselecting values (2000)

    Using Data | Validation, choose List from the Allow drop down.
    In the source box, type the values you want in the list. If the values are dates, you could insert a new sheet and type the current date and auto fill as far as you need to. Then select the date range you created as the source for the list box. (It is not a good idea to have too many dates to choose from!)
    Then in the other tabs, you can choose to include an error msg, if the person types and invalid value!

    PS: About the date field. You can use the key combination of : CTRL + ; (Semi Colon) to insert the current date very quickly without typing!
    Regards,
    Rudi

  7. #7
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autoselecting values (2000)

    I have done this but don't know how to select a value from the lists l have created?
    Please can you tell me how l do this, thanks.

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

    Re: Autoselecting values (2000)

    Click in the cell where you want to enter a version number or date.
    A dropdown arrow should appear to the right of the cell.
    Click on the dropdown arrow to make the list appear.
    Click on the item you want.

  9. #9
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autoselecting values (2000)

    I have tried this but l cannot see the dropdown, see screenshot.

  10. #10
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autoselecting values (2000)

    I have got the drop down list working but for some reason it is not showing the correct values.
    see screenshot.It should show all the dates in the column specified.

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autoselecting values (2000)

    See the attached sample file.

    Where are you getting stuck JJ? Do you need more help?
    Regards,
    Rudi

  12. #12
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autoselecting values (2000)

    I cannot get the dropdown to appear please can you tell me what l am doing wrong in order for this not to appear?

  13. #13
    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: Autoselecting values (2000)

    Could you attach an example file that does not work correctly so we can see what you set up?

    Steve

  14. #14
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autoselecting values (2000)

    I have now geot it work , l simple copied the attached example, thanks for your help.

  15. #15
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autoselecting values (2000)

    OK, lets start from the beginning again...(I will use the date drop down example)

    1. Create a list of dates in the Z column ( for example ). Select Z1 and type Jan 2006. Select Z2 and type Feb 2006. Select both these typed dates and autofill (the black handle on the bottom corner of the selection) the selection to create a list of dates...say to Dec 2007.

    Now that you have created the list of dates that will go into the drop down do the following:

    1. Select the range of cells where the drop down arrow must appear. For example, select the cells C2:C100 if each cell in this range must have a drop down that will contain a date.
    2. Once the range is selected, choose Data | Validation
    3. From the Allow drop down, choose List
    4. Click in the source box that appears
    5. Scroll to cell Z1 and select the list of dates you created so that the reference Z1:Z24 appears in the source box.
    6. Click on the error alert tab and type a message similar to: The value you selected is not valid, please select from the list!
    7. Choose OK

    The cells in the C column should now have an arrow appear on the right when you click to select them. IE: Cell C2 will now display an arrow when you select it. Click on the arrow and you should see your list of dates appear.

    I hope these instructions will help you!
    Regards,
    Rudi

Page 1 of 2 12 LastLast

Posting Permissions

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