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
www.dropbox.com
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 | ||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | |||||||||||||
1 | Per selected weight chosen | per 100 | ||||||||||||||||||||||||||||||||||||||||||
2 | Material | Weight chosen | Cost | pallets required | Fuel req to transport | number of trips | number of drivers | Weight | weight + packaging | packaging required | Name | Cost | pallets required | Fuel req to transport | number of trips | number of drivers | Weight | weight + packaging | packaging required | Cost | pallets required | Fuel req to transport | number of trips | number of drivers | Weight | weight + packaging | packaging required | |||||||||||||||||
3 | > x | |||||||||||||||||||||||||||||||||||||||||||
4 | < x | |||||||||||||||||||||||||||||||||||||||||||
5 | Type | Fn | High/Low | High/Low | Tag 1 | Tag 2 | Prep Level | Cost Per | ||||||||||||||||||||||||||||||||||||
6 | Pack Size (g) | No of Servings | Serving Size (g) | Per (g) | Location 1 | Location 2 | Brand | |||||||||||||||||||||||||||||||||||||
7 | flour | milk | VegN | VegT | Soy | Season | ||||||||||||||||||||||||||||||||||||||
8 | ||||||||||||||||||||||||||||||||||||||||||||
Build3 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
B4:B5 | Expression | =AND($B$1<>"",OR(ISNUMBER(SEARCH($B$1,$A4:$BB4,1)))) | text | NO |
Dropbox
Last edited: