Create a dynamic search list from user typed values

[askinner]

Hi there,
I have pretty much exhausted all potential ways of doing this.
Basically, what I am after is for a user to type a value into the SEARCH field. Using a combination of vlookup, index or match - a list of values containing the search parameter from the Inv!B2:B5949 would be displayed in the PRODUCT NAME field.

I have tried fiddling around with a dynamic list from another forum - but always ends up with N/A values.

At the moment - the user has to type the INVCode from the Inv Sheet into the Recipe sheet for it to pull the values across.

Any help or pointing in the right direction, would be greatly appreciated.

[rylo]

Hi

So if a user typed LOWENBRAU into Menu Item Cost!C8, the you would get a list of all 17 items that have that word in column B of Inv available for selection in D8? By Data Validation or something similar? Is that correct?

rylo

[askinner]

Hi Rylo,
Totally correct. I have scoured every source imaginable trying to work out how to do it.

[cnodnarb]

I would solve this by inserting hidden rows with the inventory...

for blank cells betwixt I would use a tick ' to allow the autocomplete...

I'm going to try to work something up either tonight or tomorrow.

[EDIT]
Getting nowhere fast. If anybody else has ideals, might want to help out!

[rylo]

Hi

Try this

Right click on the Menu Item Cost tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("C8:C20")) Is Nothing And Len(Target.Value) > 0 Then  '.Address = "$C$8" Then
    Set Rng = Sheets("Inv").Range("B2:B6000")
    holder = ""
    For Each ce In Rng
      If InStr(1, ce, Target.Value) > 0 Then
        holder = holder & ce.Value & ","
      End If
    Next ce
    If Len(holder) > 0 Then
      With Target.Offset(0, 1).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=holder
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
      End With
    
    End If
  ElseIf Len(Target.Value) = 0 Then
    With Target.Offset(0, 1).Validation
        .Delete
    End With
  
  End If
End Sub

Now type BIER in C8, and move to D8. You should find a drop down list of relevant items.

HTH

rylo

[FDibbins]

I think I may have a solution that will get you headed in the right direction. I created a helper column on sheet2 and used this...
=IF(ISNUMBER(SEARCH('Menu Item Cost'!$C$8,Inv!$B2,1)),MAX($J$1:J1)+1,"")
this searches for the data enetered in sheet1 C8, and numbers the returns
for the next part, I wasnt sure of the distinction btw primary and secondary ingredients, so I put this in secondary, copied down...
=IFERROR(INDEX(Inv!$A$2:$J$6000,MATCH(ROW(A1),Inv!$J$2:$J$6000,0),2),"")
for the rest of the columns, you can just change the column number (,2) accordingly

see attached

(note: i removed the "table: function on sheet 2, you can make it a table again if you want. I find it easier to create formulas from "non-table" tables)

[cnodnarb]

Well, this is as best I could do.

Apparently excel stops trying to autocomplete after the first 50 items or so, you can consider this one too though.

Hmm...file is too big...am I allowed to link like this? (better to ask forgiveness than permission!)


https://dl.dropbox.com/u/28521507/ex...odnarb%29.xlsm

[FDibbins]

@ cnod, preferably not, because not every1 can access sites like dropbox (i cant from work), you could maybe try to zip the file and then send it?

[cnodnarb]

@ fdibbins
Noted! Will try that next time around

[askinner]

Thanks guys for the quick responses.
The solution from fdibbins works quite seamlessly.

[FDibbins]

Happy to help. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)