tring to add a search cell

jamtay317

Well-known Member
Joined
Mar 6, 2013
Messages
769
I have a data base that I want to search where if I type Sm* it will bring up all of the last names that start with Sm. also useing the same cell I would like to be able to search lot numbers and sales order numbers. I have a data set, please tell me what or where I need to add for someone can help me with the VBA code.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Hi,

I can help you with searching for a Partial Text Lookup...
Excel 2007 or 2003 Magic Trick 801: Partial Text Lookup Formula To Return Multiple Records - YouTube

Sample data and example results based on the formula method in the above link....

Excel Workbook
ABCDEFG
1ID NumberNameAmountAnimalTransportDate
2AA10000Bart Simpson368catBike01/01/2011
3AA10010Lisa Simpson277dogHelicopter02/01/2011
4AA10012Grandpa Simpson474HorseBus03/01/2011
5AA10003Lisa Simpson163Mousetrain04/01/2011
6AA10004Marge Simpson330fishHelicopter05/01/2011
7AA10050Maggie Simpson346catPlane01/01/2011
8AA10006Milhouse Van Houten472PandaTut Tut07/01/2011
9AA10007Ned Flanders343TigerCoach08/01/2011
10AA10008Otto Mann300LionCarriage09/01/2011
11AA10009Rod Flanders343TigerBus10/01/2011
12AA10010Tod Flanders100cowtrike01/01/2011
13AA10012Moe Szyslak147dogCar01/01/2011
14AA10013Grandpa Sim267HorseHelicopter04/01/2011
15AA10014Lisa Sim458Mousetrain05/01/2011
16AA10015Marge Simpson436HorseTram06/01/2011
17AA10020Bart Sim496TigerBoat08/01/2011
18
19SearchCount
20Si10
21ID NumberNameAmountAnimalTransportDate
22AA10000Bart Simpson368catBike40544
23AA10010Lisa Simpson277dogHelicopter40545
24AA10012Grandpa Simpson474HorseBus40546
25AA10003Lisa Simpson163Mousetrain40547
26AA10004Marge Simpson330fishHelicopter40548
27AA10050Maggie Simpson346catPlane40544
28AA10013Grandpa Sim267HorseHelicopter40547
29AA10014Lisa Sim458Mousetrain40548
30AA10015Marge Simpson436HorseTram40549
31AA10020Bart Sim496TigerBoat40551
32
Sheet11



The formula used in A22 needs entering with ctrl shift enter NOT just enter, it can then be copied across and down.
You can copy the results to another sheet and the sheet reference will be added.

This formula "may" be used for what you require, eg searching for different criteria, which I assume will be based in different columns, but I haven't tested it.

I hope this helps.

Ak
 
Last edited:
Upvote 0
thanks for the video but I'm getting an error now can someone look and help please?

=IF(ROWS(B$7:B7)>F2,"",INDEX(ExDrexelInvoices!$D$5:$D$2000,(SMALL(IF(ISNUMBER(SEARCH($F$3,ExDrexelInvoices!$F$85:$F$2000))+(ISNUMBER(SEARCH($F$3,ExDrexelInvoices!$G$85:$G$2000))+(ISNUMBER(SEARCH($F$3,ExDrexelInvoices!$F$85:$F$2000)))),ROW(ExDrexelInvoices!$D$5:$D$2000)-ROW(ExDrexelInvoices!$D$5)+1),ROWS(B$7:B7)))))
 
Upvote 0
Hi,

Looking at your formula...

=IF(ROWS(B$7:B7)>F2,"",INDEX(ExDrexelInvoices!$D$5:$D$2000,(SMALL(IF(ISNUMBER(SEARCH($F$3,ExDrexelInvoices!$F$85:$F$2000))+(ISNUMBER(SEARCH($F $3,ExDrexelInvoices!$G$85:$G$2000))+(ISNUMBER(SEARCH($F$3,ExDrexelInvoices!$F$85:$F$2000)))),ROW(ExDrexelInvoices!$D$5:$ D$2000)-ROW(ExDrexelInvoices!$D$5)+1),ROWS(B$7:B7)))))

Does this ExDrexelInvoices!$D$5:$D$2000 need to be ExDrexelInvoices!$D$85:$D$2000
Or this ExDrexelInvoices!$F$85:$F$2000 to this ExDrexelInvoices!$F$5:$F$2000
The F2 needs to be "locked" $F$2
$F $3 should be $F$3

Ak
 
Upvote 0
=IF(ROWS($B$7:$B7)>$F$2,"",INDEX(ExDrexelInvoices!D$5:D$2000,SMALL(IF($F$3=CHOOSE($I$2,ExDrexelInvoices!$B$5:$B$2000,ExDrexelInvoices!$D$5:$D$2000,ExDrexelInvoices!$F$5:$F$2000,ExDrexelInvoices!$G$5:$G$2000,ExDrexelInvoices!$J$5:$J$2000),ROW(ExDrexelInvoices!$D$5:$D$2000)-ROW(ExDrexelInvoices!$D$5)+1),ROWS($B$7:$B7))))
this is curently what I'm useing tring to get rid of the choose function and get search function in there
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top