Creating a search and "jump to" function
I have a spreadsheet with a significant number of tool details on a number of sheets (broken down by market type). I have a front page that summarises the other sheets, but what I want to add to the front page is a search function that then enables a hyperlink function to take the user to the location in the spreadsheet where that tool number's details are located. I can use the VLOOKUP function to search for the tool number and verify it's existance, but I dont know how to use the functions or write a macro or VBA to hyperlink to that tool number. Any suggestions? Brisebear. -- Brisebear ------------------------------------------------------------------------ Brisebear's Profile: http://www.excelforum.com/member.php...o&userid=37689 View this thread: http://www.excelforum.com/showthread...hreadid=572958 |
Creating a search and "jump to" function
You ready for some work?
I would insert a new worksheet (later hide this sheet). In column A would be the tool part number. In column B would be the location that I need to jump to. And create range names for each of these locations--the name of that range would go in column B. Debra Dalgleish has tips on naming ranges: http://www.contextures.com/xlNames01.html So for instance the table would look like: Part# Jump to 12345 Cate001 12346 Cate002 12347 Cate001 (I figured multiple part numbers would jump to the same spot) .... Then name the data in column A so that it can be used in a Data|Validation cell in the original worksheet (say A1). Then put this formula in B1. =if(a1="","",HYPERLINK("#"&VLOOKUP(A1,hiddensheet! a:b,2,FALSE),"click me")) Brisebear wrote: I have a spreadsheet with a significant number of tool details on a number of sheets (broken down by market type). I have a front page that summarises the other sheets, but what I want to add to the front page is a search function that then enables a hyperlink function to take the user to the location in the spreadsheet where that tool number's details are located. I can use the VLOOKUP function to search for the tool number and verify it's existance, but I dont know how to use the functions or write a macro or VBA to hyperlink to that tool number. Any suggestions? Brisebear. -- Brisebear ------------------------------------------------------------------------ Brisebear's Profile: http://www.excelforum.com/member.php...o&userid=37689 View this thread: http://www.excelforum.com/showthread...hreadid=572958 -- Dave Peterson |
Creating a search and "jump to" function
Thanks for the reply Dave, I have tried it, but there are some teething problems, which hopefully you can iron out for me with some clarifications: 1. When you say "Create range names for each of these locations", do you mean for each individual "tool number" for for the tools located in specific worksheets? 2. When you suggest that Column B be the location you need to jump to, do you mean that I can do a cell reference by, for example selecting Cate002 and referencing it by going "=Retail!A2" or does it have to be set up in a different manner? I look forward to your advise as so far it is the closest I have come to resolving this issue. Brian.:) -- Brisebear ------------------------------------------------------------------------ Brisebear's Profile: http://www.excelforum.com/member.php...o&userid=37689 View this thread: http://www.excelforum.com/showthread...hreadid=572958 |
Creating a search and "jump to" function
#1. Yep. The program has to know where to jump for each part number. But they
don't have to be unique "jump to" locations. You could have multiple part numbers jump to the same location--maybe a header cell for that type of tool. (All hammers go to the hammer "jump to" point--no matter the actual type.) #2. Column B would hold Cate002. But you'd have a name Cate002 (insert|Name|define) that pointed at retail!a2. So it sounds like you got what I meant. === And alternative would be to use a macro that would look at the part number and then search all the sheets looking for that part number. The macro could be invoked by a worksheet_event (dependind on the version of excel you're using) or maybe even a button that the user clicks. Brisebear wrote: Thanks for the reply Dave, I have tried it, but there are some teething problems, which hopefully you can iron out for me with some clarifications: 1. When you say "Create range names for each of these locations", do you mean for each individual "tool number" for for the tools located in specific worksheets? 2. When you suggest that Column B be the location you need to jump to, do you mean that I can do a cell reference by, for example selecting Cate002 and referencing it by going "=Retail!A2" or does it have to be set up in a different manner? I look forward to your advise as so far it is the closest I have come to resolving this issue. Brian.:) -- Brisebear ------------------------------------------------------------------------ Brisebear's Profile: http://www.excelforum.com/member.php...o&userid=37689 View this thread: http://www.excelforum.com/showthread...hreadid=572958 -- Dave Peterson |
All times are GMT +1. The time now is 03:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com