Autocomplete Validation List

  • hi

    Well i am in a bit of a rock and hard place..

    I help out a friend who runs a company who has basic excel skills.. but has written spreadsheet to do his job for other to use.

    I been trying to teach him but its hard work as he is old and it just doesnt sink in..and he wishes to stay in control of the work he is done.. so out goes out vba...

    i trying to help input on the sheet with data validation and trying to get it working with the autocomplete work around..

    i took this from a page of this site...


    List is Dynamic and/or Resides on Another Worksheet
    Lets say your list is on Sheet2 and you wish to use the Validation List with AutoComplete on Sheet1.

    On Sheet1 A1 Enter =Sheet2!A1 and copy down including as many spare rows as needed (say 300 rows total). Hide these rows and use this formula in the Refers to: for a dynamic named range called MyList:


    Now in the cell immediately below the last hidden row use Data Validation and for the List Source use =MyList

    << snip

    And i just cant seem to get it working i might be missing something.. but would someone mind doing an example of how it works.. or any other alternatives for restricted data with the chance of data validation please

    Many thanks


  • Re: Autocomplete Validation List

    FYI I couldn't get the autocomplete aspect to work either (Xl2003)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!