Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List
Hi
I have another problem. With the validation list i have just created it is really long with names. I was wanting to make it that if you typed in the first few letters of the Clients name it short listed the dropdown to what was matching the entered data. The end selection will be from the dropdown this is to make it easier to find the clients name. Is this possible?? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List
Hi
Not possible with data validation lists. A walkaround: Create a sheet p.e. List, with a table Char1, Name A2=IF($B2="","",LEFT($B2)) Copy formula in A2 down, and fill column B with names. Sort the table alpabetically! (NB! always you edit the names list, you have to sort the table!) Define named range p.e. Char1=OFFSET(List!$A$1,1,,COUNTIF(List!$A:$A,""&" """)-1,1) On your data entry table (I assume the row 1 contains column headers), you must have 2 columns like Group and Name Select some range in column Group, and apply data validation list with source=Char1 to it. Select any cell in row 2 on data entry sheet, and define a named range Name=OFFSET(List!$B$1,MATCH(Sheet1!$A2,List!$A:$A, 0)-1,,COUNTIF(List!$A:$A,Sheet1!$A2),1) Select the range in column Name (as much rows as you selected in Group column before), and apply data validation list with source =Name Select a character into Group column - now you can select only names beginning with this caracter into Name column in same row (and nothing, when no group is selected before) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Andrew C" wrote in message ... Hi I have another problem. With the validation list i have just created it is really long with names. I was wanting to make it that if you typed in the first few letters of the Clients name it short listed the dropdown to what was matching the entered data. The end selection will be from the dropdown this is to make it easier to find the clients name. Is this possible?? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List
Sorry, but the named range Char1 don't give you an unique list of starting
characters. You have to create a separate table for it. You can enter all alphabet caracters into it, and create a range Char1 as static one - when you select a character for which is no name in names list, the Names data validation list will be empty. Or you use formulas to create the unique character list automatically from names list, and define the named range Char1 as dynamic one (I don't have enough time at moment to find a passing formula). -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
update data validation list with new entries?? | Excel Discussion (Misc queries) | |||
drop-down list validation won't allow a different worksheet | Excel Worksheet Functions | |||
Expanding Data validation from List | Excel Discussion (Misc queries) | |||
validation list with drop down list of options?? | Excel Worksheet Functions | |||
list validation using list validation... | Excel Worksheet Functions |