Search Function?

[Acrid]

Hello everyone, sorry to bother everyone, but I have spent hours searching for this answer and cant seem to find it.

I use an excel spreadsheet to keep track of my DVD collection. It's a basic spreadsheet, and only has 4 columns.

I would like to create a separate sheet that only has a search function on it.

If I enter a keyword, such as "Aladdin", it should display all results with that word, and the information in the other 3 columns.

It should also be able to find and display partial matches as well. For example, If i enter "add" it should locate and display Aladdin, the addams family, etc.

Any help would be greatly appreciated.

Thanks

 

[Acrid]

If still blank results, try this:
=IF($B$4="","",IFERROR(INDEX('DVD''s'!A$2:A$300,SMALL(IF(ISNUMBER(SEARCH($B$4,'DVD''s'!$A$2:$A$300)),ROW('DVD''s'!$B$2:$B$300)-MIN(ROW('DVD''s'!$B$2:$B$300))+1),ROWS('DVD''s'!$1:1))),""))

(Array Formula) hit CTRL-SHIFT-ENTER Button together and copied down

Azumi

maybe its my work pc, because this one didnt work either. ill have to try at home.

 

[hiker95]

Acrid,

Sample raw data in worksheet DVD's:

Excel 2007
	A	B	C
1	Movie Name	Kid Oriented	BR
2	10th Kingdom, The	Yes
3	16 Blocks
4	2012 Apocalypse
5	28 Days Later
6	28 Weeks Later
7	30 Days of Night Dark Days
8	40 Days & 40 nights
9	8 Below
10	8 Crazy Nights
11	A Lot Like Love
12	Absolute Zero / Disaster Zone: Volcano in NY
13	Addams Family, The
14	After Earth
15	Aladdin	Yes
16	Alice in Winderland	Yes
17	All Dogs go to heaven 1 & 2	Yes
DVD's

For this to work you will have to add the titles in cells C11:E11.

Excel 2007
	A	B	C	D	E
1
2
3
4	Search Title Here:
5
6
7
8
9
10	Results:
11			Movie Name	Kid Oriented	BR
12
13
14
15
16
17
18
19
20
21
Search

If you enter into cell D4 the search do, you will get this:

Excel 2007
	A	B	C	D	E
1
2
3
4	Search Title Here:		do
5
6
7
8
9
10	Results:
11			Movie Name	Kid Oriented	BR
12			10th Kingdom, The	Yes
13			All Dogs go to heaven 1 & 2	Yes
14			Armageddon
15			Black Hawk Down
16			Dodgeball
17			Donnie Darko
18			Doom
19			Double Jeopardy
20			Down Periscope
21
Search

If you delete what is in cell D4, you will get this:

Excel 2007
	A	B	C	D	E
1
2
3
4	Search Title Here:
5
6
7
8
9
10	Results:
11			Movie Name	Kid Oriented	BR
12
13
14
15
16
17
18
19
20
21
Search

If you enter : into cell D4, you will get this:

Excel 2007
	A	B	C	D	E
1
2
3
4	Search Title Here:		:
5
6
7
8
9
10	Results:
11			Movie Name	Kid Oriented	BR
12			Absolute Zero / Disaster Zone: Volcano in NY
13			Family Guy: The Untold Story
14
15
16
17
18
19
20
21
Search

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C

2. Select the worksheet in which your code is to run, worksheet Search

3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 01/20/2014, ME751411
Dim lr As Long, nr As Long, lra As Long
Dim c As Range, firstaddress As String
If Range("C4") = "" Then
  lr = Cells(Rows.Count, "C").End(xlUp).Row
  If lr > 11 Then Range("C12:E" & lr).ClearContents
  Exit Sub
End If
If Not Intersect(Target, Range("C4")) Is Nothing Then
  With Application
    .EnableEvents = False
    .ScreenUpdating = False
    lr = Cells(Rows.Count, "C").End(xlUp).Row
    If lr > 11 Then Range("C12:E" & lr).ClearContents
    With Sheets("DVD's").Columns(1)
      Set c = .Find("*" & Target.Value & "*", LookIn:=xlValues, LookAt:=xlWhole)
      If Not c Is Nothing Then
        firstaddress = c.Address
        Do
          nr = Sheets("Search").Range("C" & .Rows.Count).End(xlUp).Offset(1).Row
          Sheets("Search").Cells(nr, 3).Resize(, 3).Value = Sheets("DVD's").Cells(c.Row, 1).Resize(, 3).Value
          Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstaddress
      End If
    End With
    .EnableEvents = True
    .ScreenUpdating = True
  End With
End If
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then make changes in worksheet Search, cell D4.

 

[Rick Rothstein]

Ok, sorry, Inever saw post 3 for some reason ......

You never acknowledged it, so I am wondering if you ever saw my comments in Message #9 yet or not?