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

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

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

#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