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!!
Position cursor on bottom right corner of B" until you get the "+" (cross) , left click then drag down column
Pl see file attached.
New Range Name ValList_Ecolumn created.formula for data validation for [B2 is
Copy validation for other cells
=IF(B2="",ValList_Ecolumn,B2)
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.
Copy B2 (Validated cell)
Select the required range
Paste special--> Validation-->OK
Thanks for the reply...but that also doesn't work...
Yes the formula is the problem. Why are you restricting the DV list (variable length)?
Hi John, I merely follow the instruction of the video. I supposed it's for the searchable drop down list to work? Thanks
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...
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.
Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column = 2 Then If Target.Rows.Count > 1 Then Exit Sub Range("D1") = ActiveCell.Address End If End Sub
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.
Last edited by JohnTopley; 01-19-2016 at 04:28 PM.
Thanks John. But may I ask have you attached the wrong file...?
Sorry ... correct one here!
Thank you so much John, it seems ok! Let me try it out further. Thanks!!
Define the range ValList_Ecolumn as dynamic as below
=INDIRECT("Sheet5!$E$2:$E$"&COUNTA(Sheet5!$E$2:$E$20000)+1)
The guy has done a follow up video that I think may help.
https://www.youtube.com/watch?v=0QrQT9D25Xk
Windy
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks