Home |
Search |
Today's Posts |
#1
|
|||
|
|||
automate hyperlink
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. |
#2
|
|||
|
|||
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! GEORGIA wrote: 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. -- Dave Peterson |
#3
|
|||
|
|||
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. "Dave Peterson" wrote: 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! GEORGIA wrote: 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. -- Dave Peterson |
#4
|
|||
|
|||
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. GEORGIA wrote: 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. "Dave Peterson" wrote: 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! GEORGIA wrote: 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. -- Dave Peterson -- Dave Peterson |
#5
|
|||
|
|||
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... "Dave Peterson" wrote: 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. GEORGIA wrote: 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. "Dave Peterson" wrote: 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! GEORGIA wrote: 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. -- Dave Peterson -- Dave Peterson |
#6
|
|||
|
|||
with further research i found this formula:
=HYPERLINK(CELL("address",INDEX(sheet2!A10:A40,MAT CH(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" wrote: 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... "Dave Peterson" wrote: 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. GEORGIA wrote: 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. "Dave Peterson" wrote: 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! GEORGIA wrote: 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. -- Dave Peterson -- Dave Peterson |
#7
|
|||
|
|||
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.) GEORGIA wrote: with further research i found this formula: =HYPERLINK(CELL("address",INDEX(sheet2!A10:A40,MAT CH(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" wrote: 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... "Dave Peterson" wrote: 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. GEORGIA wrote: 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. "Dave Peterson" wrote: 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! GEORGIA wrote: 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. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
|
|||
|
|||
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! "Dave Peterson" wrote: 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.) GEORGIA wrote: with further research i found this formula: =HYPERLINK(CELL("address",INDEX(sheet2!A10:A40,MAT CH(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" wrote: 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... "Dave Peterson" wrote: 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. GEORGIA wrote: 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. "Dave Peterson" wrote: 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! GEORGIA wrote: 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. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
|
|||
|
|||
Glad you got it working.
GEORGIA wrote: 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! "Dave Peterson" wrote: 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.) GEORGIA wrote: with further research i found this formula: =HYPERLINK(CELL("address",INDEX(sheet2!A10:A40,MAT CH(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" wrote: 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... "Dave Peterson" wrote: 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. GEORGIA wrote: 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. "Dave Peterson" wrote: 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! GEORGIA wrote: 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. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run a Hyperlink from another cell | Excel Discussion (Misc queries) | |||
Importing Data from an Access Database Including a Hyperlink Colum | Excel Discussion (Misc queries) | |||
Intra-workbook hyperlink: macro/function to return to hyperlink ce | Excel Discussion (Misc queries) | |||
Importing Access File with Hyperlink | Excel Discussion (Misc queries) | |||
Removing hyperlink | Excel Discussion (Misc queries) |