06-25-2019 04:05 PM
06-25-2019 04:05 PM
I'm trying to create a function of the following thing:
Search on the sheet "2", in a column "B" (where exist a bunch of cells full of text) a cell that contains info from 2 cells from sheet "1" in columns "A" and "B". For example: On sheet "1" a have 1 cell with "Apple" and the second cell with "Pear", and i need to find on sheet "2" column "B", a recipe that contains "Apple" and "Pear" (those words coming from the two cells from sheet 1), even if those two words are separated inside the cell on sheet "B". Then create a drop down with the results of the search previously made.
I'm attaching 2 files with this example to make it easier to understand the idea.
06-25-2019 06:39 PM
Your request is simple , however you have multiple issues with the setup:
1- These are not two "Sheets", these are 2 separate Files "Workbooks". which is NOT an ideal option. Better have the source data (recipes), Your Conditions(Apple, Pear,...) and the final result used for creating drop lists into one single Excel file.
2- The data where we search would better be split into 2 columns say: Recipe name & Recipe details
Once you have a much better setup, tens or hundreds of recipes, and assuming that you will always search using 2 conditions (not necessary Apple & Pear) then the steps are as follows:
1- Create a search function that searches for the keywords
2- Create an Advanced Filter (Data Tab >> Advanced)
3- Run the filter automatically every time the search term changes
I am attaching a sample file with different data where we want to extract records based on 2 conditions selected from 2 drop lists 'region" & "Sales Rep"
this is exactly what you need. Test by changing the Region or Sales Rep from their drop lists
Note : the source list (like your Recipes) is in columns A:F
The conditions (drop lists) are in Cells H2 & I2
The extracted records are in columns K to P
let me help you with an action Plan:
Try to create a list of Recipes more organized and split the data into columns, Having Headers such as: recipe code, Recipe Name, Ingredients, How to prepare...etc
Create 2 lists with the different ingredients you will look for.
i can then help you create the functionality.
a friendly advice, do not use Excel with the same approach we use a word editor. break the data so that Excel can help you extract records.
06-25-2019 11:28 PM
i have managed to get list with recipes, take a loom at the attachment. Is you enter column A or column B the list will be generated automatically.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngRow As Long
Dim lngRowMax As Long
Dim strBuf As String
If Target.Column < 3 Then
With Cells(Target.Row, 3).Validation
lngRowMax = Sheet2.Range("B" & Sheet2.Rows.Count).End(xlUp).Row
For lngRow = 2 To lngRowMax
If InStr(LCase(Sheet2.Range("B" & lngRow).Value), LCase(Cells(Target.Row, 1).Value)) > 0 And _
InStr(LCase(Sheet2.Range("B" & lngRow).Value), LCase(Cells(Target.Row, 2).Value)) > 0 Then
strBuf = strBuf & Sheet2.Range("B" & lngRow).Value & "-"
strBuf = Left(strBuf, Len(strBuf) - 1)
strBuf = Replace(strBuf, ";", ".")
strBuf = Replace(strBuf, ".", ".")
strBuf = Replace(strBuf, ",", ".")
strBuf = Replace(strBuf, "-", ",")
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:=xlBetween, Formula1:=strBuf
06-26-2019 07:46 AM
@nabilmourad Hey, thanks for your correction related to the "Sheets", haha, I'm pretty new on excel universe, and thanks for you suggestion. Your file is incredible, but it doesn't solve my problem, unfortunetaly. The problem is, there is already a file with all the data related to the recipes, with the following headers: Recipe Name, Author, Descriptions, Ingredients and finally How to do it (the data that exist on "Sheet 2" attached in my post). Since the data "Apple" and "Pear", are surronded by a bunch of text in a single cell inside the column "How to do it", i would need a function that is able to search in those cells (Column "How to do it") those two words "Apple" and "Pear", even if they are not close to each other inside the cell. And then filter, exatly as your "Recipe Example" file does. Does such function exist?
by npfox1 on July 14, 2020
by phbalanced on May 07, 2020
by Prash Shirolkar on September 25, 2018