alternate way of data validation

[sudipto3003]

dear all,

I know how to create a data validation using a list from a another sheet. but the problem I am facing here is that when the list is too long using data validation it is difficult to find a specific word I am looking for. what I need a alternate way of data validation where I can search my desired word like in filter mode. for example I have uploaded an excel file here, where in sheet2 I have the main table, and in sheet1 I am looking from it. in sheet1 at row A3 after using alt+down arrow I have to scroll for a long time to find out the desired word. here I am looking for a alternative way out.

 

[Xiq]

Hi Supipto,
How about a search field kind of solution? Please check the attached file

 

[sudipto3003]

hi xiq,

thanks for your soon reply, but if you notice that may be there are several names with different code, like egaro howrah maidan and then howrah maidan, how to find then the exact one?

 

[lohithsriram]

Hi Sudipto,

Check this site. This talks about your requirement.

http://www.contextures.com/xlDataVal10.html

 

[sudipto3003]

HI lohithsriram,

thanks for your reply, but its not fulfil my requirement.

 

[Xiq]

Hi Sudipto,

In the attached file I added a helper table. In this table you will get up to 15 possible matches, returning the Name and Code. These matches are based on the last cell you edited (so this will change every time you do something!).

So here are the steps:

• Input a (partial) name, code or from the data-validation list in the input column.
• If the partial name has multiple matches, you see these in the helper table.
• If you need one of the other possible matches (the first match is already selected), you enter the corresponding code in the input cell.
• ...
• Profit!

How you like it

 

[lohithsriram]

Hi Sudipta,

You get the Combobox when you double click on a cell and that allows you to enter the text and it acts as as suggestion & autocomplete tool to help you to search your desired list value. Are you aiming at something different?

Is that something like an Autofilter option what you are expecting, which gives you more option to choose , search, sort etc.? That looks to be difficult combining with DV.

 

[sudipto3003]

hi lohithsriram & xiq,

may be its my fault to clear my requirement, I have attached here with an another file where in sheet1 at column K I will choose the name of the cost centre and with the help of the vlookup at column J I need the code. as you have seen the name of the cost centres list is too long and I need several rows to find the exact one. may I clear my problem now?

 

[Xiq]

Hi Sudipto,

I gave you an alternative (see attached file for an implementation), what did you think of that? A bit of feedback would be helpful.

 

[sudipto3003]

thank you xiq, now I have found what I am looking for, just one thing more, would you set the helper column at sheet1 at row M2? thanks one more time genius...

 

[Xiq]

thank you xiq, now I have found what I am looking for, just one thing more, would you set the helper column at sheet1 at row M2? thanks one more time genius...

Hi Sudipto,
If you referring to the helper table on sheet1, you can move it around yourself like a normal picture (because it is one ).

 

[sudipto3003]

hi xiq,

you are right, but it is in picture format, I can't copy a text from that, so if possible set the helper column at sheet1, please.

 

[Xiq]

Hi Sudipto,
Not sure why you want to copy a text from it (I wouldn't recommend it), but here you go.
Be careful when you do try to copy from it -- because they are all formulas you can easily break -- and remember to copy the value, instead of the formula.
Regards!
Xiq

 

[sudipto3003]

thanks xiq, its totally solved my problem now, I will be careful when should I copy from there and paste the value.