Creating a Knowledge bank

[fitzyseverton]

Hi Guys, Hope someone can help and even tell me if what im trying to do is possible.

Im trying to create all the dumb questions im asked into one searchable database

the users would scroll and forget how to use search so want to make easy for them

Ive got 2 buttons in my head linking to Q&A sheet.

1. A search button to enter what you are looking for
2. Ask new question button which will load up a email form for them to send me

Ive tried a few ways i thought but hitting a wall

 

[Logit]

Would you be willing to post your examples to a download site for review ? Paste the link here to download.

 

[Logit]

Here is a very simple project (without the email feature) to use as a possible beginning.

Sub CreateKnowledgeDatabase()
    ' Create workbook structure
    Dim wsVisible As Worksheet, wsHidden As Worksheet
    Dim wsNameVisible As String, wsNameHidden As String

    ' Define sheet names
    wsNameVisible = "SearchInterface"
    wsNameHidden = "KnowledgeBase"

    ' Create or reference visible worksheet
    On Error Resume Next
    Set wsVisible = ThisWorkbook.Worksheets(wsNameVisible)
    If wsVisible Is Nothing Then
        Set wsVisible = ThisWorkbook.Worksheets.Add
        wsVisible.Name = wsNameVisible
    End If
    wsVisible.Visible = xlSheetVisible
    wsVisible.Activate
    On Error GoTo 0

    ' Create or reference hidden worksheet
    On Error Resume Next
    Set wsHidden = ThisWorkbook.Worksheets(wsNameHidden)
    If wsHidden Is Nothing Then
        Set wsHidden = ThisWorkbook.Worksheets.Add
        wsHidden.Name = wsNameHidden
    End If
    wsHidden.Visible = xlSheetVeryHidden
    On Error GoTo 0

    ' Setup visible worksheet (Search Interface)
    With wsVisible
        .Cells.Clear
        .Cells(1, 1).Value = "Enter Search Term(s):"
        .Cells(1, 1).Font.Bold = True
        .Cells(1, 2).Value = "Search"
        .Cells(1, 2).Font.Bold = True
        .Cells(1, 2).Interior.Color = RGB(173, 216, 230)
        .Columns(1).ColumnWidth = 20
        .Columns(2).ColumnWidth = 50
        
        ' Add search button
        Dim btn As Object
        Set btn = .Buttons.Add(300, 5, 100, 20)
        btn.Caption = "Search"
        btn.OnAction = "SearchKnowledgeBase"
    End With

    ' Setup hidden worksheet (Knowledge Base)
    With wsHidden
        .Cells.Clear
        .Cells(1, 1).Value = "Question"
        .Cells(1, 2).Value = "Answer"
        .Cells(1, 1).Font.Bold = True
        .Cells(1, 2).Font.Bold = True
    End With
End Sub

Sub SearchKnowledgeBase()
    Dim wsVisible As Worksheet, wsHidden As Worksheet
    Dim searchTerm As String, terms() As String, i As Long, j As Long, lastRow As Long
    Dim output As String, term As String

    ' Define worksheet references
    Set wsVisible = ThisWorkbook.Worksheets("SearchInterface")
    Set wsHidden = ThisWorkbook.Worksheets("KnowledgeBase")

    ' Get the search term(s)
    searchTerm = wsVisible.Cells(2, 2).Value
    If searchTerm = "" Then
        MsgBox "Please enter a search term.", vbExclamation
        Exit Sub
    End If

    ' Split the search terms into an array
    terms = Split(searchTerm, " ")

    ' Search the knowledge base
    lastRow = wsHidden.Cells(wsHidden.Rows.Count, 1).End(xlUp).Row
    output = ""
    For i = 2 To lastRow
        For j = LBound(terms) To UBound(terms)
            term = Trim(terms(j))
            If term <> "" And _
               (InStr(1, wsHidden.Cells(i, 1).Value, term, vbTextCompare) > 0 Or _
                InStr(1, wsHidden.Cells(i, 2).Value, term, vbTextCompare) > 0) Then
                output = output & "Question: " & wsHidden.Cells(i, 1).Value & vbCrLf & vbCrLf & _
                                 "Answer: " & wsHidden.Cells(i, 2).Value & vbCrLf & "-----------------------" & vbCrLf & vbCrLf & vbCrLf
                Exit For
            End If
        Next j
    Next i

    ' Display results
    If output = "" Then
        MsgBox "No results found for the entered terms.", vbInformation
    Else
        MsgBox "Search Results:" & vbCrLf & vbCrLf & output, vbInformation
    End If
End Sub

Download example workbook : Internxt Drive – Private & Secure Cloud Storage

 

[fitzyseverton]

Thanks Logit, sorry for delay weekend away from work

Heres a link Knowledge Bank.xlsm

 

[fitzyseverton]

Thanks Logit, sorry for delay weekend away from work

Heres a link Knowledge Bank.xlsm

Ive now got the email part working just the search to fix

Would you be willing to post your examples to a download site for review ? Paste the link here to download.

Got email bit working now just the search causing me issues

Knowledge Bank.xlsm

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

 

[Logit]

Looks good and it appears you have it well under control. Best wishes.

 

[fitzyseverton]

thanks still struggling getting it to return a value, sooooo frustrating

 

[fitzyseverton]

Looks good and it appears you have it well under control. Best wishes.

im hitting brick wall now got the functionality working but not bringing up fields. Any ideas

Code used

=FILTER(Questions!A2:D500,ISNUMBER(SEARCH(D22,Questions!A2:D500)),"none")

 

[Logit]

I am more comfortable with macros. If you require a formula you'll be best served by posting a new query on the forum and outline your needs.

Here is the macro :

Sub lookupValue()
    Dim lookupValue As String
    Dim result As Variant
    Dim searchRange As Range
    
    ' Define the lookup value
    lookupValue = InputBox("Enter the value to look up:")
    
    ' Define the range to search in (Q&A sheet, A2:C10)
    Set searchRange = Sheets("Q&A").Range("B2:C10")
    
    ' Perform the lookup
    On Error Resume Next
    result = Application.WorksheetFunction.VLookup(lookupValue, searchRange, 2, False)
    On Error GoTo 0
    
    ' Display the result
    If Not IsError(result) Then
        MsgBox "The lookup result is: " & result
    Else
        MsgBox "Value not found"
    End If
End Sub