VBA code, worksheet_SelectionChange to create a searchable drop-down box

[JackDanIce]

Hi,

Not having much joy with this. There are some non-VBA suggestions but I can't get them to work how I need.

In an input sheet, I have a defined table (used for input area).

In this table, on a a specific column, I want to create a searchable drop-down box only when the user selects a cell in that column, where the user can start typing into a search box and the selectable list shrinks to show only values containing the characters typed. This needs to occur for each row in the given column in the table.

In a separate data sheet, I have a defined table with the entire list of values the drop-down should have.

Finally, after a value has been selected by user, I want to add 2 formula to the adjacent columns on the right.

I want to avoid using UserForms, any suggestions?

TIA,
Jack

 

[JackDanIce]

Making some progress, combing VBA and guide from this video:

The dynamic formula I have is:

=SORT(UNIQUE(FILTER(tbl_MetaData[Drop-Down Values],ISNUMBER(SEARCH(Sales!$C$14,tbl_MetaData[Drop-Down Values])),"not found")))

However, when I run macro to change part in red, the formula updates to:

=@SORT(UNIQUE(FILTER(tbl_MetaData[Drop-Down Values],ISNUMBER(SEARCH(Sales!$C$14,tbl_MetaData[Drop-Down Values])),"not found")))

Even though I haven't included @ in my code or typed it into the cell.

How can I prevent Excel auto-inserting the @? When it doesn't exist I have a dynamic array of results. After macro runs and the @ is added, it only shows a single result (losing the full list of matches)

 

[JackDanIce]

r.Formula2 = _
            Replace("=SORT(UNIQUE(FILTER(tbl_MetaData[Drop-Down Values],ISNUMBER(SEARCH(#ADD,tbl_MetaData[Drop-Down Values])),""not found"")))", "#ADD", sheetname & "!" & rng.Address)

Needed to use .Formula2