#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Andrew C
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Arvi Laanemets
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Arvi Laanemets
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
update data validation list with new entries?? cjtj4700 Excel Discussion (Misc queries) 10 December 12th 05 02:00 AM
drop-down list validation won't allow a different worksheet justmetn Excel Worksheet Functions 4 September 15th 05 05:33 PM
Expanding Data validation from List mark hansen Excel Discussion (Misc queries) 2 September 4th 05 01:39 AM
validation list with drop down list of options?? luke013 Excel Worksheet Functions 1 August 31st 05 01:16 PM
list validation using list validation... Patrick G Excel Worksheet Functions 1 December 21st 04 01:37 AM


All times are GMT +1. The time now is 03:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"