+ Reply to Thread
Results 1 to 25 of 25

Create a search tool within Excel Sheet?

  1. #1
    Registered User
    Join Date
    04-04-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    29

    Create a search tool within Excel Sheet?

    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

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,814

    Re: Create a search tool within Excel Sheet?

    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

  3. #3
    Registered User
    Join Date
    04-04-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Create a search tool within Excel Sheet?

    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
    Attached Files Attached Files

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,814

    Re: Create a search tool within Excel Sheet?

    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
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-04-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Create a search tool within Excel Sheet?

    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.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,814

    Re: Create a search tool within Excel Sheet?

    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

  7. #7
    Registered User
    Join Date
    04-04-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Create a search tool within Excel Sheet?

    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.

  8. #8
    Registered User
    Join Date
    04-04-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Create a search tool within Excel Sheet?

    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.


  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,814

    Re: Create a search tool within Excel Sheet?

    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

  10. #10
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: Create a search tool within Excel Sheet?

    couldnt you just press

    CTRL+F and use excels own find function?
    The Importance of INDEX - A GUIDE TO INDEX'S OTHER USES
    <--- If a post helps hit the star

  11. #11
    Registered User
    Join Date
    04-04-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Create a search tool within Excel Sheet?

    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?

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,814

    Re: Create a search tool within Excel Sheet?

    Quote Originally Posted by Vermilion View Post
    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.

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,814

    Re: Create a search tool within Excel Sheet?

    Quote Originally Posted by Vermilion View Post
    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

  14. #14
    Registered User
    Join Date
    04-04-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Create a search tool within Excel Sheet?

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

  15. #15
    Registered User
    Join Date
    10-04-2012
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Create a search tool within Excel Sheet?

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

    regards

    Quote Originally Posted by Vermilion View Post
    Ok will check it out. Anyway, the formula coupled with the drop menu works wonders.

  16. #16
    Registered User
    Join Date
    11-15-2012
    Location
    Boston
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Create a search tool within Excel Sheet?

    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

  17. #17
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Create a search tool within Excel Sheet?

    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.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  18. #18
    Registered User
    Join Date
    11-08-2012
    Location
    Letter tofnent
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Create a search tool within Excel Sheet?

    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?

  19. #19
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,627

    Re: Create a search tool within Excel Sheet?

    @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.
    Ben Van Johnson

  20. #20
    Registered User
    Join Date
    04-20-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Create a search tool within Excel Sheet?

    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

  21. #21
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Create a search tool within Excel Sheet?

    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.

  22. #22
    Registered User
    Join Date
    11-19-2013
    Location
    Cardiff
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Create a search tool within Excel Sheet?

    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

  23. #23
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Create a search tool within Excel Sheet?

    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.

  24. #24
    Registered User
    Join Date
    12-22-2015
    Location
    Pune
    MS-Off Ver
    2007
    Posts
    1

    Re: Create a search tool within Excel Sheet?

    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

  25. #25
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,814

    Re: Create a search tool within Excel Sheet?

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1