Create a search tool within Excel Sheet?

[Vermilion]

Hi all,

Got myself in a pickle. I've created a spreadsheet containing 200+ entries for my companies suppliers. These include their name/code, contact emails and contact number. This list is far more easily accessible as it saves the user having to bring up the suppliers details within our own bespoke software. However what seemed like a nifty spreadsheet has quickly turned into a giant mush of details with no way to quickly find the one you want without scrolling down to the one you want.

Is there a way I can create a function/tool that can allow me to type the contact name in cell "X" and have Excel take me to the suppliers line in the table?

Cheers

[Pete_UK]

You can use a HYPERLINK function to do that, in conjunction with MATCH. Post an example file so that I can suggest the exact form of the formula to suit your layout.

Hope this helps.

Pete

[Vermilion]

As the excel file contains contact details I've made a (very) simple table design similar to the one I'm using. All I want is to be able to type somewhere, the name of the supplier I want (furthest left column) and have excel take me to that row.

Thanks

[Pete_UK]

Okay, I've set this up so that you can enter the name of the store in G1 (yellow cell) and then click on "go there" in H1 to jump to the appropriate row. H1 contains this formula:

=HYPERLINK("#Sheet1!A"&MATCH(G1,A:A,0),"go there")

so you should be able to see how to modify it if necessary.

You could put data validation on G1 using the names in column A as the source, so that you could just pick the name from a drop-down list.

Hope this helps.

Pete

[Vermilion]

Thanks for the formula, but I don't seem to be able to apply it to my own sheet without recieving the "Invalid Reference" error box. I've got the formula set as:
=HYPERLINK("SUPPLIER DETAILS!A"&MATCH(G1,A:A,0),"go there")

I suspect it to be something to do with the link name, but "Sheet1" doesn't work either.

[Pete_UK]

If your details are on a sheet called Supplier Details then you need to include apostrophes around the sheet name because of the space. Also, you still need the # symbol, which effectively means "in this workbook", so try this:

=HYPERLINK("#'Supplier Details'!A"&MATCH(G1,A:A,0),"go there")

Hope this helps.

Pete

[Vermilion]

All I see now is the #N/A on the field containing the formula.
Strange isn't it? The data is in a table, but other than that your formula should work. The Supplier names (the item I'm searching) is in column A, and I'm typing my "search" into G1.

[Vermilion]

Even stranger.....I've just C&P the formula into a different cell, copied it back in the original cell, and it works

Thanks a lot Pete. Wouldn't have known where to start. Would be handy to be able to search using just part of the name, but the formula does the job well on its own.

[Pete_UK]

Well, that means that you don't have an exact match. That's why I suggested in an earlier post that you use data validation in G1 using the list of names as the source. You may have typed in an extra space or have an extra space in the list of names, or just mis-spelt the name. Try again with some other names to verify that the formula is working.

Hope this helps.

Pete

[twiggywales]

couldnt you just press

CTRL+F and use excels own find function?

[Vermilion]

Yeah, I've got validation on - allowing me to select the name from a drop list in G1. Is it possible to have the name auto-fill whilst I type it, using the drop list?

[Pete_UK]

Would be handy to be able to search using just part of the name, but the formula does the job well on its own.

You can use a wildcard character with MATCH. Try this:

=HYPERLINK("#'Supplier Details'!A"&MATCH(G1&"*",A:A,0),"go there")

Then just put a partial name in G1 and click the link - it will take you to the first record that matches.

Hope this helps.

Pete

If you think the Thread has been Solved, please mark it as such (the FAQ tells you how). Also, you can click on the "star" icon in the bottom left corner of any post that has helped you, in order to pass on thanks directly.

[Pete_UK]

Is it possible to have the name auto-fill whilst I type it, using the drop list?

Too many posts going on at the same time here, that's why I'm quoting sections.

Data Validation drop-downs do not have an AutoComplete feature, but you could use a Combo box to achieve that. Debra Dalgleish shows how here, if you want to take it further:

http://www.contextures.com/xlDataVal10.html

Hope this helps.

Pete

[Vermilion]

Ok will check it out. Anyway, the formula coupled with the drop menu works wonders.

[pablo_jt6]

Look at this "excelminiapps.blogsp..." in google. I found a nice solution there...

regards

Ok will check it out. Anyway, the formula coupled with the drop menu works wonders.

[bon77]

This is a nice tool, is there a way to make it search a range as opposed to a column. I tried to expand it to search a2 through g75 but could not get it to work.

thanks, Bill

[arlu1201]

bon77,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[pareshshah]

Thats helpful, but how do I assign that search ink in cell 'H1' to a shape. So instead of clicking on that cell I can slick on a shape?

[protonLeah]

@pareshshah;
Did you read the comment above your post?
Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[jordan682]

Hi there,

I am wanting to make a search box in excel but don't have a clue how to do it, I have 3 rows of data in sheet 1 and am wanting to make a search box and be able to type anything in and pull information out of the 3 rows and into a results.

Can anyone help me?> I have watched the videos on youtube but they are quite confusing!

Thanks
Jordan

[arlu1201]

jordan682,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

[griffithslaura07]

hi, not sure if i will get an answer as this was posted a while ago but ive used the suggested formula and it all seems to work but when i click on the hyperlink i get an error message pop up saying 'cannot open specified file' what does this mean and how can i solve this problem?
Thank you for any replies

[arlu1201]

griffithslaura07,

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[Manish1989]

Can any one tell me how I make a searching tools.
For ex: I have database in which every day I have to find account number for a perticular phone number.
I am doing this task manually everyday, this task repeated every day 200 times and wasting my time.
Phone AccountNumber
7186477734 0511267870
7183854728 0341224180
7183844310 0034009847
7186281082 0093550289
3479873632 0013571428
7183830748 0021030251
7183496854 0012645166

[Pete_UK]

There are four other responses above yours that say to other posters "Do not post a question in the thread of another member -- start your own thread."

What part of this do you not understand?

Pete