Fast Search Entire Excel Workbook & Return All Results

Rjitsoft 

Thanks for the response above (though somewhat delayed!) .

To do what you want, I've modified  the attached file to add the VBA module from the tutorial  Fast Search Entire Excel Workbook & Return All Results and made these changes:

1. Added the Dashboard sheet from the tutorial and changed headings in row 10 to match those in your sheet1
2. Modified the List data validation in cell E4 to:"Delivery, Invoice No, LR No."
3. Changed the VBA code as follows (changes and comments in bold:

Sub Data_Search()
' TeachExcel.com
' modified for 3 columns

Dim ws As Worksheet
Dim dashboard As Worksheet
Dim dataArray As Variant
Dim datatoShowArray As Variant

'Application.ScreenUpdating = False 'Turning off for the macro can speed things up - not so much here though.

'Dashboard sheet
Set dashboard = Sheets("Dashboard")

'Data table information
dataColumnStart = 1
dataColumnEnd = 9
dataColumnWidth = dataColumnEnd - dataColumnStart ' Number of columns for the raw data (+1 not included because it makes lower calculations more confusing)
dataRowStart = 2
dashboardDataColumnStart = 2 ' Column for the data on the dashboard

'Get user input
searchValue = dashboard.Range("C4").Value
fieldValue = dashboard.Range("E4").Value

'Clear Dashboard
Call Clear_Data

'Figure out by which field we will search.
If (fieldValue = "Delivery") Then
    ' make it column 4
    searchField = 4
ElseIf (fieldValue = "Invoice No") Then
    ' needs column 5
    searchField = 5
Else
    ' then assume it's Lr No., column 8
    searchField = 8
End If

'Loop through the worksheets
For Each ws In Worksheets

You can now use the Dashboard selections to search sheet1 (and any others you add with the same format).

Hope this fixes your problem. If so, please be sure to mark this Answer as Selected.

Hi rjitsoft and welcome.

It's good that you provided a "demo" file, but I need a bit more clarification. In your demo file, is that sheet a sample of a worksheet to search or the dashboard? Do you want the search to include all 3 criteria (Delivey, Invoice, and LR No) or jsut one? Do you want the search to return only the 3 criteria or the entire row of data?
Performing a search of the entire workbook is possible, just need to know the details.

You may find Don's macro example ( on this site ) helpful:  Search Entire Workbook

If Don's macro does what you need please mark this answer as selected

Cheers   :-)