Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    68

    Unanswered: autotext feature?

    Hello,
    If you have data validation set on a cell that uses a List Source with Claim Number values contained on another sheet, can you have an autotext feature which would populate the claim number when a few letters are typed in the cell?

    Thanks for you assistance.

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    >If you have data validation set on a cell that uses a List Source with Claim Number values contained on another sheet

    I've ever been able to get data validation to work on a list from another sheet.

    >can you have an autotext feature which would populate the claim number when a few letters are typed in the cell?

    AutoText and data validation are two different things... There is "AutoCorrect Options" which you can manually setup, and "Auto Complete". You must be talking about Auto Complete which is a setting in the Options on the Edit Tab. Its usually set by default. Auto complete will fill in the number when your working in a list. but it only starts when Excel can differentiate the item, so if you have lots of similar code numbers it won't do you much good.

    You can set the data validation options to only allow items contained in the list if you need to validate to the list. Honestly I don't use the data validation lists often. the drop down arrows on the cells get in the way and if you change the sheet columns/rows much you can mess up the list when located on the same sheet. Another trick that may help, Right click in a cell in your list and select "Pick From DropDown List..." from the shortcut menu. It's already built in and works great, but this only gives you items already listed.


    .
    ~

    Bill

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    Quote Originally Posted by savbill
    >If you have data validation set on a cell that uses a List Source with Claim Number values contained on another sheet

    I've ever been able to get data validation to work on a list from another sheet.
    This should work. The easiest way is to name the range, then use that in the Validation List.

    For instance, on Sheet2, put a few numbers in column B (say B2:B5), then go to

    Insert > Name > Define, give it a name Rng1, and in the Refers to box, type this (which allows the list to grow automatically):

    =OFFSET(Sheet2!$B$2,0,0,COUNTA(Sheet2!$B:$B),1)

    Then on Sheet1, select a cell, then Data > Validation, and in the Validation Crieteria, from the dropdown list, choose List. In the Source, type:

    =Rng1

    Click OK. Now click on the dropdown list, and you will see the selections.

    >can you have an autotext feature which would populate the claim number when a few letters are typed in the cell?
    It's not an ideal situation, but one workaround, depending on how many items you have, is to use rows above your data, and fill in all the options. Then hide those rows. Now the AutoComplete capability will help you.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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