Is it possible to have a cell fill in from a list tied to the cell once you start typing in the cell? (e.g. - a cell has a drop down list tied to it with all states. If you type an "a" in the cell it automatically fills in Alabama which is the first state in the list tied to the cell. If you continued typing it would fill with the next possible entry from the list. so "ar" would bring in Arizona.) Fairly common in programmed forms but not sure if it can be put in a spreadsheet?
Go to Tools>Options - Edit and check Enable AutoComplete for cell values
Move your list of items into the range immediately above the Validation cell.
Hide the rows above your Validation cell.
Now, as you type Excel will look at the list above the Validation cell and AutoComplete based on it.
If the 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:
=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
I hope it helps.
-------------
Tony
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks