automate hyperlink

G

GEORGIA

I have a summary sheet which contains each associate's name grouped by the
team name. I also have different tabs seperated by team name which contains
details information of associate. I would manually look for the name and
insert a hyperlink so that if a manager clicks on the name it will take to
the detail. because associates are being added/deleted or moved to a
different team quite often, is there a way to automate this hyperlink
process? for
example:
look for a name "Sandy" in all the sheet, if found change it to hyperlink
connecting to where her detail sheet? (showing her name as a text on the
hyperlink)
Any help would greatly appreicated.
 
D

Dave Peterson

If all those associate names are in one column, maybe you could just insert an
additional column with a formula in it:

=HYPERLINK("#"&"'"&A1&"'!a1","click me")
and drag down

Then if A1 contains a worksheet name, when you click on that hyperlink, off
you'll go!
 
G

GEORGIA

It did not work.. i know i am doing something wrong. I don't quite understand
the formual...sorry
what is "#" ?
I have replaced"A1" with where the name is located on my summary page like
this:
=HYPERLINK("#"&""&H23&"!H23","click me")
is saying that name is invalid.

Thank you for your help.
 
D

Dave Peterson

My sample wasn't as nice as it could have been:

With Sandy in H23, put this formula in I23:

=HYPERLINK("#"&"'"&H23&"'!a1","click me")

Watch that single quote ' -- you dropped it in your formula.

The H23 contains the worksheet name to go to. The A1 is the cell that will be
the one selected.

The # sign is an indicator for the subaddress in the link.

You may have seen it used in links to web sites:
http://www.contextures.com/xlfaqApp.html#AlreadyOpen

It means to go to that "label" alreadyopen on that web page.
 
G

GEORGIA

I apologize for my ignorance..... it is giving me a message saying "
reference not valid" this time.. :-(
let me give you more details on my worksheet...

the name "Sandy" is located in Sheet1, A25.
Her detail page is located somewhere in column A in sheet2.
so when i put the formual in b25, should create a hyperlink if the name is
found, so that I am able to click on it and takes me to wherever her name
exist in sheet2.

Thank you for your patient.. i am learning...
 
G

GEORGIA

with further research i found this formula:
=HYPERLINK(CELL("address",INDEX(sheet2!A10:A40,MATCH(A39,sheet2!A10:A40,0))),"Sally")

however, it is giving me " cannot open specified file". But the hyperlink
address is giving me the correct addres....sally is located in a36 in sheet2,
and that is what the address says where i put this formula.
help!
thank you!

GEORGIA said:
I apologize for my ignorance..... it is giving me a message saying "
reference not valid" this time.. :-(
let me give you more details on my worksheet...

the name "Sandy" is located in Sheet1, A25.
Her detail page is located somewhere in column A in sheet2.
so when i put the formual in b25, should create a hyperlink if the name is
found, so that I am able to click on it and takes me to wherever her name
exist in sheet2.

Thank you for your patient.. i am learning...
Click to expand...
 
D

Dave Peterson

What happened to Sandy???

This worked ok for me:

=HYPERLINK("#"&CELL("address",INDEX(Sheet2!A10:A40,MATCH(A39,Sheet2!A10:A40,0))),
"Sally")

(still all one cell)

(I thought that you wanted to be taken to a worksheet named Sandy from your
original description.)
 
G

GEORGIA

THIS WORKED SOOOOO WONDERFULLY!!
THANK YOU!
oh, i got Sandy and Sally mixed up. :)

you are my life saver!! I will take you out for dinner if i ever meet you
in person! :)
THANKS AGAIN!
 
D

Dave Peterson

Glad you got it working.
THIS WORKED SOOOOO WONDERFULLY!!
THANK YOU!
oh, i got Sandy and Sally mixed up. :)

you are my life saver!! I will take you out for dinner if i ever meet you
in person! :)
THANKS AGAIN!
Click to expand...
 
Top