Searching with Excel

[clemkonan]

I have an audit coming up and I need to be able to find information quickly to satisfy the auditor

At A1 I have the thing I need information on this is my search box :

A1= Risk assessment ( I want to know which clause this is under ( the clause # ) and what the clause says)

B1: B52 = a range of clause numbers for example 1.11, 1.3, 4,2, 7,4, etc

C1: C52 = a range of clauses for example
The company shall undertake a documented risk assessment of each raw material or group of raw materials including packaging to identify potential risks to product safety, legality and quality.

In this case the clause number I need is 1.11 and the clause wording is as above.

A1 is going to be my serch box here I enter what I am looking for. How can I set his up without visual basic so that it returns not an identical match but all clauses and clause numbers matching my query.

If visual basic is my only hope then reluctantly I accept.

Thanks for your help.

 

[Logit]

.
I have a small project that will do what you are wanting .. but ... (yes there is a caveat) ... it won't be perfect.
By that I mean you could search for the term "risk" or "assessment" or "risk assessment" or "raw" or "material" or "raw material" ... or you could search for the
clause number if you know it ( 1.11 or 1.3 or 4.2, etc).

This is part of the code :

Sub FilterClear()
Dim sh As Shape
Application.ScreenUpdating = False


With ActiveSheet
On Error Resume Next


    ActiveSheet.ListObjects("Searchtable").Range.AutoFilter


    ActiveSheet.ListObjects("Searchtable").Range.AutoFilter
ActiveSheet.TextBox1.text = ""
.Range("A5").Select
End With
    Application.ScreenUpdating = True
End Sub

It combines VBA with formulas. Let me know if you are interested and I can post it for download from another site.

 

[clemkonan]

.
I have a small project that will do what you are wanting .. but ... (yes there is a caveat) ... it won't be perfect.
By that I mean you could search for the term "risk" or "assessment" or "risk assessment" or "raw" or "material" or "raw material" ... or you could search for the
clause number if you know it ( 1.11 or 1.3 or 4.2, etc).

This is part of the code :

Sub FilterClear()
Dim sh As Shape
Application.ScreenUpdating = False


With ActiveSheet
On Error Resume Next


    ActiveSheet.ListObjects("Searchtable").Range.AutoFilter


    ActiveSheet.ListObjects("Searchtable").Range.AutoFilter
ActiveSheet.TextBox1.text = ""
.Range("A5").Select
End With
    Application.ScreenUpdating = True
End Sub

It combines VBA with formulas. Let me know if you are interested and I can post it for download from another site.

Thanks, yes please send me the rest of the solution

 

[Logit]

.
https://www.amazon.com/clouddrive/share/vFEtKVlvWhz66iMULEB9qGJjIezPeaHKr3J0sxia93r

Don't edit the far right columns. Those have formulas that make the entire thing function.

 

[clemkonan]

.
https://www.amazon.com/clouddrive/share/vFEtKVlvWhz66iMULEB9qGJjIezPeaHKr3J0sxia93r

Don't edit the far right columns. Those have formulas that make the entire thing function.

Thanks I will give it a try and post back