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]

Follow the link Im fix them for you, hope it works

http tps://www.dropbox.com/s/mfqy1jm4qtlx08r/DVD List.xlsx

The main formula to search with some keywords :
=IFERROR(IF(OR($B$4="",ROWS(B$13:B13)>$B$10),"",INDEX('DVD''s'!$A$2:$A$10000,AGGREGATE(15,6,(ROW('DVD''s'!$A$2:$A$10000)-ROW('DVD''s'!$A$2)+1)/ISNUMBER(SEARCH($B$4,'DVD''s'!$A$2:$A$10000)),ROWS(B$13:B13)))),"")

More clearer see the file

Regards
Azumi

Azumi, this is exactly what I was Looking for! However, for some reason if I change what is in the search box, for example "bourne" It will change to 3 titles, but will not update the list populated below, it just changes to all -.

Acrid,

You are posting pictures/graphics. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense.

One last try:

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots per the above, then:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

Hiker, I will see what I can do, Iwill post it if iIcan manage at work , or as soon as Ican get home .

 

[Acrid]

hiker, here is what you wanted, but its only part of my data. it will not accept more than 100 rows.

Excel 2010
	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
18	American Pie
19	Anger Management
20	Armageddon
21	Arthur & the Invisibles 2 & 3	Yes
22	Avatar
23	Backdraft
24	Bad Boys II
25	Bambi	Yes
26	Beauty & The Beast	Yes
27	Beethoven 1-3	Yes
28	Bill Engvall Heres your Sign
29	Billy Madison
30	Black Hawk Down
31	Blade
32	Blade II
33	Blazing Saddles
34	Blue Collar Comedy Tour
35	Boat Trip
36	Bolt	Yes
37	Bourne Identity
38	Bourne Supremecy
39	Bourne Ultimatum
40	Brave Little Toaster, The	Yes
41	Brother Bear	Yes
42	Cars	Yes
43	Cars 2	Yes
44	Cast Away
45	Cellular
46	Charlie Brown's Christmas Tales	Yes
47	Charlottes Web Cartoon	Yes
48	Charlottes Web Movie	Yes
49	Chronicles of Narnia, The	Yes
50	Chronicles of Riddick
51	Click
52	Conair
53	Constantine
54	Cops & Robbersons
55	Core, The
56	Curse of King Tuts Tomb, The
57	Dark Knight, the
58	Day After Tomorrow, The		Blu-Ray
59	Dj vu
60	Despicable Me
61	Die Hard		Blu-Ray
62	Disaster Collectors Set
63	Dodgeball
64	Donnie Darko
65	Doom
66	Double Jeopardy
67	Down Periscope
68	Dreamer	Yes
69	Elysium
70	Enchanted	Yes
71	ET	Yes
72	Event Horizon
73	Exit Wounds
74	Expendables, The
75	Family Guy: The Untold Story
76	Fast & Furious Movies
77	FernGully The Last Rainforest	Yes
78	Ferris Buellers Day Off
79	Final Destination 1
80	Final Destination 2
81	Final Destination 3
82	Final Destination 4
83	Final Destination 5
84	Finding Nemo	Yes
85	Firewall
86	Four Brothers
87	Funny Farm
88	Furry Vengeance	Yes
89	Ghostbusters 1
90	Ghostbusters 2
91	Ghostrider
92	Gnomeo & Juliet	Yes
93	Gone in 60 Seconds
94	Goonies, The	Yes
95	Green Mile, The
96	Grownups
97	Happy Feet	Yes
98	Happy Gilmore
99	Hard Luck
100	Harry & The Hendersons
DVD's

 

[azumi]

In my computer, the formula is workin, Did you set the formula setting in your excel to automatic calculation?

 

[azumi]

Or what excel version do you have?

 

[azumi]

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

 

[Acrid]

In my computer, the formula is working, Did you set the formula setting in your excel to automatic calculation?

Honestly, not sure how to change it, whatever default is.

Or what excel version do you have?

I have access to 2003/2007/2010. Right now 2007 is what I was using.

 

[hiker95]

Acrid,

So your raw data looks like this:

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
DVD's

Now we need a screenshot of worksheet Search.

And, I will not be able to supply a formula solution.

But, the Worksheet_Change event will do it automatically for you per my former screenshots in my reply #3.

Is this acceptable to you?????

 

[Acrid]

Acrid,

So your raw data looks like this:

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
DVD's

Now we need a screenshot of worksheet Search.

And, I will not be able to supply a formula solution.

But, the Worksheet_Change event will do it automatically for you per my former screenshots in my reply #3.

Is this acceptable to you?????

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

Screenshot of worksheet "search" is in post 7, first picture.

Perfectly acceptable solution.
Please note that I will be adding a 4th column in the near future.

I did enter your VB Formula as described in post 3, and at the moment it is not working for me. I have not used VB at all, but I have an extremely basic knowledge. What reference should I change "sheet 1" to? "DVD's" or "Search"?

 

[hiker95]

Acrid,

Screenshot of worksheet "search" is in post 7, first picture.

So that I can get it right, I do not want to manually create what the Search worksheet is going to look like.

Please post another screenshot similar to your DVD's screenshot in your reply [B]#12[/B].

 

[Acrid]

Acrid,



So that I can get it right, I do not want to manually create what the Search worksheet is going to look like.

Please post another screenshot similar to your DVD's screenshot in your reply [B]#12[/B].

I believe this is what you wanted?:

Excel 2010
	A	B	C	D	E	F	G	H
1
2
3
4	Search Title Here:
5
6
7
8
9
10	Results:
11
12
13
14
15
16
17
18
19
20
Search