Excel 2010 how to use autocomplete in validation list

Question

I m using a large validation list on which a couple of vlookup   functions depend  This list is getting larger and larger  Is there a way to type the first letters of the list item I m looking for  instead of manually scrolling down the list searching for the item   I ve done some Googling but this suggests that this is indeed possible in earlier versions of Excel  but not in Excel 2010  Hope you guys can help

User · Answer

Building on the answer of JMax  use this formula for the dynamic named range to make the solution work for multiple rows    OFFSET Sheet2  A 1 MATCH INDIRECT  Sheet1   amp ADDRESS ROW   COLUMN   4   amp     Sheet2  A 1  A 300 0 -1 0 COUNTA Sheet2  A  A

User · Answer

Here is a very good way to handle this  found on ozgrid    Let s 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    OFFSET Sheet1  A 1 0 0 MATCH     Sheet1  A 1  A 300 -1  1    Now in the cell immediately below the last hidden row use Data Validation and for the List Source use  MyList   EDIT  Adapted version for Excel 2007   couldn t test on 2010 though but AFAIK  there is nothing really specific to a version   Let s say your data source is on Sheet2 A1 A300 and let s assume your validation list  aka autocomplete  is on cell Sheet1 A1    Create a dynamic named range MyList that will depend on the value of the cell where you put the validation   OFFSET Sheet2  A 1 MATCH Sheet1  A 1 amp     Sheet2  A 1  A 300 0 -1 0 COUNTA Sheet2  A  A   Add the validation list on cell Sheet1 A1 that will refert to the list  MyList   Caveats   This is not a real autocomplete as you have to type first and then click on the validation arrow   the list will then begin at the first matching element of your list The list will go till the end of your data  If you want to be more precise  keep in the list only the matching elements   you can change the COUNTA with a SUMLPRODUCT that will calculate the number of matching elements Your source list must be sorted

User · Answer

As other people suggested  you need to use a combobox  However  most tutorials show you how to set up just one combobox and the process is quite tedious    As I faced this problem before when entering a large amount of data from a list  I can suggest you use this autocomplete add-in   It helps you create the combobox on any cells you select and you can define a list to appear in the dropdown

User · Answer

Here s another option  It works by putting an ActiveX ComboBox on top of the cell with validation enabled  and then providing autocomplete in the ComboBox instead   Option Explicit    Autocomplete - replacing validation lists with ActiveX ComboBox     Usage      1  Copy this code into a module named m autocomplete     2  Go to Tools   References and make sure  Microsoft Forms 2 0 Object Library  is checked     3  Copy and paste the following code to the worksheet where you want autocomplete        ------------------------------------------------------------------------------------------------------        - autocomplete        Private Sub Worksheet SelectionChange ByVal Target As Range             m autocomplete SelectionChangeHandler Target        End Sub        Private Sub AutoComplete Combo KeyDown ByVal KeyCode As msforms ReturnInteger  ByVal Shift As Integer             m autocomplete KeyDownHandler KeyCode  Shift        End Sub        Private Sub AutoComplete Combo Click              m autocomplete AutoComplete Combo Click        End Sub        ------------------------------------------------------------------------------------------------------    When the combobox is clicked  it should dropdown  expand  Public Sub AutoComplete Combo Click       Dim ws As Worksheet  Set ws   ActiveSheet     Dim cbo As OLEObject  Set cbo   GetComboBoxObject ws      Dim cb As ComboBox  Set cb   cbo Object     If cbo Visible Then cb DropDown End Sub    Make it easier to navigate between cells Public Sub KeyDownHandler ByVal KeyCode As MSForms ReturnInteger  ByVal Shift As Integer      Const UP As Integer   -1     Const DOWN As Integer   1      Const K TAB        As Integer   9     Const K ENTER      As Integer   13     Const K ARROW UP   As Integer   38     Const K ARROW DOWN As Integer   40      Dim direction As Integer  direction   0      If Shift   0 And KeyCode   K TAB        Then direction   DOWN     If Shift   0 And KeyCode   K ENTER      Then direction   DOWN     If Shift   1 And KeyCode   K TAB        Then direction   UP     If Shift   1 And KeyCode   K ENTER      Then direction   UP     If Shift   1 And KeyCode   K ARROW UP   Then direction   UP     If Shift   1 And KeyCode   K ARROW DOWN Then direction   DOWN      If direction  lt  gt  0 Then ActiveCell Offset direction  0  Activate      AutoComplete Combo Click End Sub  Public Sub SelectionChangeHandler ByVal Target As Range      On Error GoTo errHandler      Dim ws As Worksheet  Set ws   ActiveSheet     Dim cbo As OLEObject  Set cbo   GetComboBoxObject ws      Dim cb As ComboBox  Set cb   cbo Object        Try to hide the ComboBox  This might be buggy        If cbo Visible Then         cbo Left   10         cbo Top   10         cbo ListFillRange              cbo LinkedCell              cbo Visible   False         Application ScreenUpdating   True         ActiveSheet Calculate         ActiveWindow SmallScroll         Application WindowState   Application WindowState         DoEvents     End If      If Not HasValidationList Target  Then GoTo ex      Application EnableEvents   False        TODO  the code below is a little fragile     Dim lfr As String     lfr   Mid Target Validation Formula1  2      lfr   Replace lfr   INDIREKTE         norwegian     lfr   Replace lfr   INDIRECT         english     lfr   Replace lfr                lfr   Application Range lfr  Address External  True       cbo ListFillRange   lfr     cbo Visible   True     cbo Left   Target Left     cbo Top   Target Top     cbo Height   Target Height   5     cbo Width   Target Width   15     cbo LinkedCell   Target Address External  True      cbo Activate     cb SelStart   0     cb SelLength   cb TextLength     cb DropDown      GoTo ex  errHandler      Debug Print  Error      Debug Print Err Number     Debug Print Err Description ex      Application EnableEvents   True End Sub    Does the cell have a validation list  Function HasValidationList Cell As Range  As Boolean     HasValidationList   False     On Error GoTo ex     If Cell Validation Type   xlValidateList Then HasValidationList   True ex  End Function    Retrieve or create the ComboBox Function GetComboBoxObject ws As Worksheet  As OLEObject     Dim cbo As OLEObject     On Error Resume Next     Set cbo   ws OLEObjects  AutoComplete Combo       On Error GoTo 0     If cbo Is Nothing Then          Dim EnableSelection As Integer  EnableSelection   ws EnableSelection         Dim ProtectContents As Boolean  ProtectContents   ws ProtectContents          Debug Print  Lager AutoComplete Combo          If ProtectContents Then ws Unprotect         Set cbo   ws OLEObjects Add ClassType   Forms ComboBox 1   Link  False  DisplayAsIcon  False                                Left  50  Top  18 75  Width  129  Height  18 75          cbo name    AutoComplete Combo          cbo Object MatchRequired   True         cbo Object ListRows   12         If ProtectContents Then ws Protect     End If     Set GetComboBoxObject   cbo End Function

User · Answer

OFFSET NameList  A 2  A 200 MATCH INDIRECT  FillData   amp ADDRESS ROW   COLUMN   4   amp     NameList  A 2  A 200 0 -1 0 COUNTIF  A 2  A 200 INDIRECT  FillData   amp ADDRESS ROW   COLUMN   4   amp      1     Create sheet name as Namelist  In column A fill list of data  Create another sheet name as FillData for making data validation list as you want  Type first alphabet and select  drop down menu will appear depend on you type

User · Answer

Excel automatically does this whenever you have a vertical column of items  If you select the blank cell below  or above  the column and start typing  it does autocomplete based on everything in the column

[vba] Excel 2010: how to use autocomplete in validation list

Examples related to vba

Examples related to excel

Examples related to excel-2010