Searchable Drop down list

[axangec]

Hi guys,

I have created a searchable drop down list after viewing this --> https://www.youtube.com/watch?v=vkPoViUhkxU

The thing is how do I copy the drop down list to one whole column instead of just one cell?

Attached is my file is where the drop down list will only works on B2 cell and when I copied it to B3 cell, the result will not be the same as B2. How do make B3 or more cells to work independently just like B2?how.xlsx

Thanks in advance!!

[JohnTopley]

Position cursor on bottom right corner of B" until you get the "+" (cross) , left click then drag down column

[axangec]

Thanks for the reply. I have done that but it does not solve the problem...

When I copy or duplicate B2 cell to other cells, it will not work as it should. Once I select eg. Salary on B2, all the other cells can only select Salary and nothing more.

Looking at it again, I think it's because column D cells are with this formula =IF(ISNUMBER(SEARCH($B$2,E2)),MAX($D$1:D1)+1,0)

Hence, may I ask how do I tweak the formula so that B2 cell can be replicate to work when copied to other cells?

Thanks.

[kvsrinivasamurthy]

Copy B2 (Validated cell)
Select the required range
Paste special--> Validation-->OK

[axangec]

Thanks for the reply...but that also doesn't work...

[JohnTopley]

Yes the formula is the problem. Why are you restricting the DV list (variable length)?

[axangec]

Hi John, I merely follow the instruction of the video. I supposed it's for the searchable drop down list to work? Thanks

[kvsrinivasamurthy]

Pl see file attached.
New Range Name ValList_Ecolumn created.formula for data validation for [B2 is

Please Login or Register  to view this content.

Copy validation for other cells

[axangec]

Thanks kvsrinivasamurthy. But that would means the drop down list is no longer searchable...

I actually have sometime similar but as you can see, the drop down list is rather long... So I thought of making the drop down list a little more dynamic which leads me to the Youtube video...

[JohnTopley]

See the attached.

This a macro attached to Sheet5 which records the address of the Active Cell in D1.

The formula for the Validation_List now looks at the address in D1 and dynamically creates a list for whatever is entered in a DV in Column B.

The drawback is you have to clear the B cell entry (Use Delete on keyboard) for the macro to determine the address.

Please Login or Register  to view this content.

in E2 and copy down

=IF(ISNUMBER(SEARCH(INDIRECT($D$1),F2)),MAX($E$1:E1)+1,0)

I also changed the DV

=IF(B2=" ",Full_List,validation_list)

"Full_List" is named range (E2;E68)

You get the "Full_list" if the B cell is empty, otherwise you get the shortened "Validation_List"

Hope this helps.

[axangec]

Thanks John. But may I ask have you attached the wrong file...?

[JohnTopley]

Sorry ... correct one here!

[axangec]

Thank you so much John, it seems ok! Let me try it out further. Thanks!!

[kvsrinivasamurthy]

Define the range ValList_Ecolumn as dynamic as below

Please Login or Register  to view this content.

[windy58]

The guy has done a follow up video that I think may help.

https://www.youtube.com/watch?v=0QrQT9D25Xk

Windy