Search function with results disappearing when criteria is deleted

mikiel111

New Member
Joined
Mar 17, 2020
Messages
38
Office Version
  1. 365
Asking because I think this is only possible with VBA
Generally speaking: I need the results of various search criteria populated in a specific area of my sheet. Once the search criteria is removed the results are also removed so that something else can be searched.

Specifically speaking:
I have all my entries for Material +their characteristics in sheet List1.

In sheet Build3, I require a search utility which lists stuff based on what i entered. Once i remove the search criteria the list shown is removed, this is vital.
The search criteria considers all the below points
1) Any Material containing the word/s in Cell B3 is a positive match. I can settle for it matching just 1 word if it is easier
2) The criteria from E5:L7 (each of these cells i`ll make into a dropdown list so the search to only considers those)
3) The required range in E3:L4 for a specific characteristic. In E3:L3 I`ll put the lower limit and in E4:L4 the upper limit of what`s needed. Example: in E3 = 20 and E4 = 30 means i`m looking for material costing between 20 & 30).

The results would start being populated from cell Z3 downwards. I think it is easier if just the name of the Material is listed here. I`ll just use drag an XLOOKUP to do some maths on all the resulting materials` characteristics. Once search criteria is altered or removed then list changes or empties in the case of all search criteria being removed

Book1.xlsx
ABCDEFGHIJKLMXYZAAABACADAEAFAGAHAIAJAKALAMANAOAP
1Per selected weight chosenper 100
2MaterialWeight chosenCostpallets requiredFuel req to transportnumber of tripsnumber of driversWeightweight + packagingpackaging requiredNameCostpallets requiredFuel req to transportnumber of tripsnumber of driversWeightweight + packagingpackaging requiredCostpallets requiredFuel req to transportnumber of tripsnumber of driversWeightweight + packagingpackaging required
3> x
4< x
5Type FnHigh/LowHigh/LowTag 1Tag 2Prep LevelCost Per
6Pack Size (g)No of ServingsServing Size (g)Per (g)Location 1Location 2Brand
7flourmilkVegNVegTSoySeason
8
Build3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B4:B5Expression=AND($B$1<>"",OR(ISNUMBER(SEARCH($B$1,$A4:$BB4,1))))textNO


 
Last edited:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

mikiel111

New Member
Joined
Mar 17, 2020
Messages
38
Office Version
  1. 365
On further research I have discovered that displaying results in a listbox could also be useful as shown in this video. However if so the requirements would change slightly as the point of use cells would bei can copy and paste results elsewhere without adding buttons etc...

see 24:11 of what i mean by displaying results in the search box
 

Forum statistics

Threads
1,143,677
Messages
5,720,259
Members
422,273
Latest member
linds75

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top