validation related

dear friends

i have used data validation method to restrict the values in a cell (i have opted for a list which is in some other range)

then when i try to select the value from the drop down box the list is not appearing in alphabetical order and this is making it difficult to select the value.

i want to know whether it is possible to select the value by typing the characters in the cell i.e. as i type the characters, i want the matching values to appear in the drop-down box so that i can either complete the entry by typing or select the value from the matches in the list.

if this is possible pls explain the procedure. this will be of great help to me

thanks
ashok

Hi,

then when i try to select the value from the drop down box the list is not appearing in alphabetical order and this is making it difficult to select the value.

If you sort the source list into alphabetical order then your data validation list will also appear in that order which will make it easier for your users.

i want to know whether it is possible to select the value by typing the characters in the cell i.e. as i type the characters, i want the matching values to appear in the drop-down box so that i can either complete the entry by typing or select the value from the matches in the list.

You can also indirectly add an "autocomplete" feature to the data validation by turning on the "enable autocomplete for cell values" option and positioning the source list directly above (hidden if you like) the cell.



An alternative to using data validation would be to use a combobox. If this interests you but you're not sure how then have a look at Debra Dalgleish's site (http://contextures.on.ca/xlDataVal10.html).

Hope that helps...

thank you dear colin legg,

it is a very good idea to position the list immediately above the cell, but in my situation the list is in other sheet.

the reference site have a lot of information regarding the validation i once again thank you for suggesting such useful site.

i have one more question about the validation.

can i change the validation list basing on the value of some other cell.

that is for example if the value in c5 is "string1" then the list should be a, b, c, d, ..... and if the value in c5 is "string2" then the list should be p, q, r, s, t, ... and likewise

thanks and regards
ashok

You cound name the different lists and use an if expression in the DV list

=IF($C$5="string1,List1,List2)

dear bob phillips,

this works good with only two alternatives. if the values of c5 are more than 2 and the lists are also more than two then how can we incorporate the criteria.

thanks and regards
ashok

Just extend the formula

=IF($C$5="string1","List1",IF($C$5="string2","List2","List3"))

thanks dear bob phillips

i think i can use like this up-to 7 levels

thanks once again

If you want more than 7, you could try this approach.

Build a table of conditional values and list names in say L1:M10, something like

string1|List1
string2|List2
string3|List3
etc.

and then use thi formula

=INDIRECT(VLOOKUP($C$5,$L$1:$M$10,2,FALSE))