Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
dropdown list to a page in worksheet
Hi,
I have a list of names in worksheet1 I have a Page for each name in Worksheet2 I need to creat a dropdown list in worksheet2 of names in worksheet1 which will allow me to jump to his specific page. how do i do this? Thanks, Jared |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
dropdown list to a page in worksheet
Hi,
Since you used the term "drop down list," I would like to highlight the following links. However, the purpose is for data entry/validation. I am not surprised if your approach may have to be something very different - a formula/procedure plus hyperlink? I'll let the experts fill in the blank. http://office.microsoft.com/en-us/as...022151033.aspx http://support.microsoft.com/default...b;en-us;211548 Please note that I had to use INDIRECT() in the case of referencing a **different** workbook (as per the second link above). Thank you for the question and I am sure I can learn something here. Epinn "Jared" wrote in message ... Hi, I have a list of names in worksheet1 I have a Page for each name in Worksheet2 I need to creat a dropdown list in worksheet2 of names in worksheet1 which will allow me to jump to his specific page. how do i do this? Thanks, Jared |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
dropdown list to a page in worksheet
Epinn,
I looked through both links, but i couldn't figure out exactly what exactly was i suppose to see in the pages. The first link got me through the list, but the second link i did not understand at all how was connected to my situation thanks "Epinn" wrote: Hi, Since you used the term "drop down list," I would like to highlight the following links. However, the purpose is for data entry/validation. I am not surprised if your approach may have to be something very different - a formula/procedure plus hyperlink? I'll let the experts fill in the blank. http://office.microsoft.com/en-us/as...022151033.aspx http://support.microsoft.com/default...b;en-us;211548 Please note that I had to use INDIRECT() in the case of referencing a **different** workbook (as per the second link above). Thank you for the question and I am sure I can learn something here. Epinn "Jared" wrote in message ... Hi, I have a list of names in worksheet1 I have a Page for each name in Worksheet2 I need to creat a dropdown list in worksheet2 of names in worksheet1 which will allow me to jump to his specific page. how do i do this? Thanks, Jared |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
dropdown list to a page in worksheet
Jared,
Shall I say the second link is a continuation of the first link. If your list of valid entries is in a **different workbook** and you follow the instructions from link 1, you may get an error message "You may not use references to other worksheets or workbooks for Data Validation criteria." I did and I had to do a get around by using the instructions in link 2. It may not be obvious to you until you try it. As I said in my first post, I had to use INDIRECT() which was the second method stated in link 2. Depending on your arrangement, you may or may not need link 2 for the fix. I included it just in case. Please look at link 1 and link 2 as part I and part II. If link 1 doesn't help you at all, you can ignore link 2. You can also forget about link 2, if you use ONE worksheet and not two workbooks. I included the links for your reference as you mentioned drop down list. That's all. Epinn "Jared" wrote in message ... Epinn, I looked through both links, but i couldn't figure out exactly what exactly was i suppose to see in the pages. The first link got me through the list, but the second link i did not understand at all how was connected to my situation thanks "Epinn" wrote: Hi, Since you used the term "drop down list," I would like to highlight the following links. However, the purpose is for data entry/validation. I am not surprised if your approach may have to be something very different - a formula/procedure plus hyperlink? I'll let the experts fill in the blank. http://office.microsoft.com/en-us/as...022151033.aspx http://support.microsoft.com/default...b;en-us;211548 Please note that I had to use INDIRECT() in the case of referencing a **different** workbook (as per the second link above). Thank you for the question and I am sure I can learn something here. Epinn "Jared" wrote in message ... Hi, I have a list of names in worksheet1 I have a Page for each name in Worksheet2 I need to creat a dropdown list in worksheet2 of names in worksheet1 which will allow me to jump to his specific page. how do i do this? Thanks, Jared |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
dropdown list to a page in worksheet
Epinn,
Okay, the first link got me through making the list i needed, but how do i add a hyper link per name? so clicking the name will get me to a specific cell. but each name needs to be to a different location. i might need to add a event click in vba, but i don't know how to add this to a list. thanks Jared "Epinn" wrote: Jared, Shall I say the second link is a continuation of the first link. If your list of valid entries is in a **different workbook** and you follow the instructions from link 1, you may get an error message "You may not use references to other worksheets or workbooks for Data Validation criteria." I did and I had to do a get around by using the instructions in link 2. It may not be obvious to you until you try it. As I said in my first post, I had to use INDIRECT() which was the second method stated in link 2. Depending on your arrangement, you may or may not need link 2 for the fix. I included it just in case. Please look at link 1 and link 2 as part I and part II. If link 1 doesn't help you at all, you can ignore link 2. You can also forget about link 2, if you use ONE worksheet and not two workbooks. I included the links for your reference as you mentioned drop down list. That's all. Epinn "Jared" wrote in message ... Epinn, I looked through both links, but i couldn't figure out exactly what exactly was i suppose to see in the pages. The first link got me through the list, but the second link i did not understand at all how was connected to my situation thanks "Epinn" wrote: Hi, Since you used the term "drop down list," I would like to highlight the following links. However, the purpose is for data entry/validation. I am not surprised if your approach may have to be something very different - a formula/procedure plus hyperlink? I'll let the experts fill in the blank. http://office.microsoft.com/en-us/as...022151033.aspx http://support.microsoft.com/default...b;en-us;211548 Please note that I had to use INDIRECT() in the case of referencing a **different** workbook (as per the second link above). Thank you for the question and I am sure I can learn something here. Epinn "Jared" wrote in message ... Hi, I have a list of names in worksheet1 I have a Page for each name in Worksheet2 I need to creat a dropdown list in worksheet2 of names in worksheet1 which will allow me to jump to his specific page. how do i do this? Thanks, Jared |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
dropdown list to a page in worksheet
Jared
Do you have the ranges named in InsertName Define? You could use event code with the DV list in B1 Option Compare Text Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("B1")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Select Case Target.Value Case "peter" Application.Goto Reference:="peter" Case "paul" Application.Goto Reference:="paul" Case "mary" Application.Goto Reference:="mary" End Select endit: Application.EnableEvents = True End Sub If ranges are not named, just replace Application.Goto Reference:="peter" with Sheets("Sheet2").Range("A1").Select A lot of work if you have a great whack of names. Gord Dibben MS Excel MVP On Tue, 29 Aug 2006 11:22:02 -0700, Jared wrote: Epinn, Okay, the first link got me through making the list i needed, but how do i add a hyper link per name? so clicking the name will get me to a specific cell. but each name needs to be to a different location. i might need to add a event click in vba, but i don't know how to add this to a list. thanks Jared "Epinn" wrote: Jared, Shall I say the second link is a continuation of the first link. If your list of valid entries is in a **different workbook** and you follow the instructions from link 1, you may get an error message "You may not use references to other worksheets or workbooks for Data Validation criteria." I did and I had to do a get around by using the instructions in link 2. It may not be obvious to you until you try it. As I said in my first post, I had to use INDIRECT() which was the second method stated in link 2. Depending on your arrangement, you may or may not need link 2 for the fix. I included it just in case. Please look at link 1 and link 2 as part I and part II. If link 1 doesn't help you at all, you can ignore link 2. You can also forget about link 2, if you use ONE worksheet and not two workbooks. I included the links for your reference as you mentioned drop down list. That's all. Epinn "Jared" wrote in message ... Epinn, I looked through both links, but i couldn't figure out exactly what exactly was i suppose to see in the pages. The first link got me through the list, but the second link i did not understand at all how was connected to my situation thanks "Epinn" wrote: Hi, Since you used the term "drop down list," I would like to highlight the following links. However, the purpose is for data entry/validation. I am not surprised if your approach may have to be something very different - a formula/procedure plus hyperlink? I'll let the experts fill in the blank. http://office.microsoft.com/en-us/as...022151033.aspx http://support.microsoft.com/default...b;en-us;211548 Please note that I had to use INDIRECT() in the case of referencing a **different** workbook (as per the second link above). Thank you for the question and I am sure I can learn something here. Epinn "Jared" wrote in message ... Hi, I have a list of names in worksheet1 I have a Page for each name in Worksheet2 I need to creat a dropdown list in worksheet2 of names in worksheet1 which will allow me to jump to his specific page. how do i do this? Thanks, Jared |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
dropdown list to a page in worksheet
Dear Gord,
I do have the names defined. but i do not want to put events under specific names. the list changes. I will add names and edit others. what are my options? Jared "Gord Dibben" wrote: Jared Do you have the ranges named in InsertName Define? You could use event code with the DV list in B1 Option Compare Text Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("B1")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Select Case Target.Value Case "peter" Application.Goto Reference:="peter" Case "paul" Application.Goto Reference:="paul" Case "mary" Application.Goto Reference:="mary" End Select endit: Application.EnableEvents = True End Sub If ranges are not named, just replace Application.Goto Reference:="peter" with Sheets("Sheet2").Range("A1").Select A lot of work if you have a great whack of names. Gord Dibben MS Excel MVP |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
dropdown list to a page in worksheet
Gord,
I found the following link on drop down list and hyperlink without using VBA. Mind you it was Excel 2000. I am not sure about the suggestion and Jared's need, and wonder what you think. One line caught my eyes - "This formula creates immediately a clickable hyperlink." http://www.mrexcel.com/archive/Formulas/19517.html Epinn "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Jared Do you have the ranges named in InsertName Define? You could use event code with the DV list in B1 Option Compare Text Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("B1")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Select Case Target.Value Case "peter" Application.Goto Reference:="peter" Case "paul" Application.Goto Reference:="paul" Case "mary" Application.Goto Reference:="mary" End Select endit: Application.EnableEvents = True End Sub If ranges are not named, just replace Application.Goto Reference:="peter" with Sheets("Sheet2").Range("A1").Select A lot of work if you have a great whack of names. Gord Dibben MS Excel MVP On Tue, 29 Aug 2006 11:22:02 -0700, Jared wrote: Epinn, Okay, the first link got me through making the list i needed, but how do i add a hyper link per name? so clicking the name will get me to a specific cell. but each name needs to be to a different location. i might need to add a event click in vba, but i don't know how to add this to a list. thanks Jared "Epinn" wrote: Jared, Shall I say the second link is a continuation of the first link. If your list of valid entries is in a **different workbook** and you follow the instructions from link 1, you may get an error message "You may not use references to other worksheets or workbooks for Data Validation criteria." I did and I had to do a get around by using the instructions in link 2. It may not be obvious to you until you try it. As I said in my first post, I had to use INDIRECT() which was the second method stated in link 2. Depending on your arrangement, you may or may not need link 2 for the fix. I included it just in case. Please look at link 1 and link 2 as part I and part II. If link 1 doesn't help you at all, you can ignore link 2. You can also forget about link 2, if you use ONE worksheet and not two workbooks. I included the links for your reference as you mentioned drop down list. That's all. Epinn "Jared" wrote in message ... Epinn, I looked through both links, but i couldn't figure out exactly what exactly was i suppose to see in the pages. The first link got me through the list, but the second link i did not understand at all how was connected to my situation thanks "Epinn" wrote: Hi, Since you used the term "drop down list," I would like to highlight the following links. However, the purpose is for data entry/validation. I am not surprised if your approach may have to be something very different - a formula/procedure plus hyperlink? I'll let the experts fill in the blank. http://office.microsoft.com/en-us/as...022151033.aspx http://support.microsoft.com/default...b;en-us;211548 Please note that I had to use INDIRECT() in the case of referencing a **different** workbook (as per the second link above). Thank you for the question and I am sure I can learn something here. Epinn "Jared" wrote in message ... Hi, I have a list of names in worksheet1 I have a Page for each name in Worksheet2 I need to creat a dropdown list in worksheet2 of names in worksheet1 which will allow me to jump to his specific page. how do i do this? Thanks, Jared |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
dropdown list to a page in worksheet
Thanks for the link. Aladin is pretty sharp.
Jared could probably use that method. Hope he is still hanging around. Gord On Tue, 29 Aug 2006 17:26:27 -0400, "Epinn" wrote: Gord, I found the following link on drop down list and hyperlink without using VBA. Mind you it was Excel 2000. I am not sure about the suggestion and Jared's need, and wonder what you think. One line caught my eyes - "This formula creates immediately a clickable hyperlink." http://www.mrexcel.com/archive/Formulas/19517.html Epinn "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Jared Do you have the ranges named in InsertName Define? You could use event code with the DV list in B1 Option Compare Text Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("B1")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Select Case Target.Value Case "peter" Application.Goto Reference:="peter" Case "paul" Application.Goto Reference:="paul" Case "mary" Application.Goto Reference:="mary" End Select endit: Application.EnableEvents = True End Sub If ranges are not named, just replace Application.Goto Reference:="peter" with Sheets("Sheet2").Range("A1").Select A lot of work if you have a great whack of names. Gord Dibben MS Excel MVP On Tue, 29 Aug 2006 11:22:02 -0700, Jared wrote: Epinn, Okay, the first link got me through making the list i needed, but how do i add a hyper link per name? so clicking the name will get me to a specific cell. but each name needs to be to a different location. i might need to add a event click in vba, but i don't know how to add this to a list. thanks Jared "Epinn" wrote: Jared, Shall I say the second link is a continuation of the first link. If your list of valid entries is in a **different workbook** and you follow the instructions from link 1, you may get an error message "You may not use references to other worksheets or workbooks for Data Validation criteria." I did and I had to do a get around by using the instructions in link 2. It may not be obvious to you until you try it. As I said in my first post, I had to use INDIRECT() which was the second method stated in link 2. Depending on your arrangement, you may or may not need link 2 for the fix. I included it just in case. Please look at link 1 and link 2 as part I and part II. If link 1 doesn't help you at all, you can ignore link 2. You can also forget about link 2, if you use ONE worksheet and not two workbooks. I included the links for your reference as you mentioned drop down list. That's all. Epinn "Jared" wrote in message ... Epinn, I looked through both links, but i couldn't figure out exactly what exactly was i suppose to see in the pages. The first link got me through the list, but the second link i did not understand at all how was connected to my situation thanks "Epinn" wrote: Hi, Since you used the term "drop down list," I would like to highlight the following links. However, the purpose is for data entry/validation. I am not surprised if your approach may have to be something very different - a formula/procedure plus hyperlink? I'll let the experts fill in the blank. http://office.microsoft.com/en-us/as...022151033.aspx http://support.microsoft.com/default...b;en-us;211548 Please note that I had to use INDIRECT() in the case of referencing a **different** workbook (as per the second link above). Thank you for the question and I am sure I can learn something here. Epinn "Jared" wrote in message ... Hi, I have a list of names in worksheet1 I have a Page for each name in Worksheet2 I need to creat a dropdown list in worksheet2 of names in worksheet1 which will allow me to jump to his specific page. how do i do this? Thanks, Jared Gord Dibben MS Excel MVP |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
dropdown list to a page in worksheet
Gord,
I am glad that you approved it. What a relief! All I had in mind was VLOOKUP and HYPERLINK but I wasn't sure about the clicking part. So I didn't feel comfortable telling Jared and was waiting for someone to fill in the blank. Then I found that link. Good thing it was Excel 2000 and not Excel 2007. ;) Thank you, Aladin! Epinn "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Thanks for the link. Aladin is pretty sharp. Jared could probably use that method. Hope he is still hanging around. Gord On Tue, 29 Aug 2006 17:26:27 -0400, "Epinn" wrote: Gord, I found the following link on drop down list and hyperlink without using VBA. Mind you it was Excel 2000. I am not sure about the suggestion and Jared's need, and wonder what you think. One line caught my eyes - "This formula creates immediately a clickable hyperlink." http://www.mrexcel.com/archive/Formulas/19517.html Epinn "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Jared Do you have the ranges named in InsertName Define? You could use event code with the DV list in B1 Option Compare Text Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("B1")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Select Case Target.Value Case "peter" Application.Goto Reference:="peter" Case "paul" Application.Goto Reference:="paul" Case "mary" Application.Goto Reference:="mary" End Select endit: Application.EnableEvents = True End Sub If ranges are not named, just replace Application.Goto Reference:="peter" with Sheets("Sheet2").Range("A1").Select A lot of work if you have a great whack of names. Gord Dibben MS Excel MVP On Tue, 29 Aug 2006 11:22:02 -0700, Jared wrote: Epinn, Okay, the first link got me through making the list i needed, but how do i add a hyper link per name? so clicking the name will get me to a specific cell. but each name needs to be to a different location. i might need to add a event click in vba, but i don't know how to add this to a list. thanks Jared "Epinn" wrote: Jared, Shall I say the second link is a continuation of the first link. If your list of valid entries is in a **different workbook** and you follow the instructions from link 1, you may get an error message "You may not use references to other worksheets or workbooks for Data Validation criteria." I did and I had to do a get around by using the instructions in link 2. It may not be obvious to you until you try it. As I said in my first post, I had to use INDIRECT() which was the second method stated in link 2. Depending on your arrangement, you may or may not need link 2 for the fix. I included it just in case. Please look at link 1 and link 2 as part I and part II. If link 1 doesn't help you at all, you can ignore link 2. You can also forget about link 2, if you use ONE worksheet and not two workbooks. I included the links for your reference as you mentioned drop down list. That's all. Epinn "Jared" wrote in message ... Epinn, I looked through both links, but i couldn't figure out exactly what exactly was i suppose to see in the pages. The first link got me through the list, but the second link i did not understand at all how was connected to my situation thanks "Epinn" wrote: Hi, Since you used the term "drop down list," I would like to highlight the following links. However, the purpose is for data entry/validation. I am not surprised if your approach may have to be something very different - a formula/procedure plus hyperlink? I'll let the experts fill in the blank. http://office.microsoft.com/en-us/as...022151033.aspx http://support.microsoft.com/default...b;en-us;211548 Please note that I had to use INDIRECT() in the case of referencing a **different** workbook (as per the second link above). Thank you for the question and I am sure I can learn something here. Epinn "Jared" wrote in message ... Hi, I have a list of names in worksheet1 I have a Page for each name in Worksheet2 I need to creat a dropdown list in worksheet2 of names in worksheet1 which will allow me to jump to his specific page. how do i do this? Thanks, Jared Gord Dibben MS Excel MVP |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
dropdown list to a page in worksheet
It works great, i am not able to actually click on the list but i does give a
button to click on. I guess that this will do the trick. Next problem: the font in the list is so small, can barely see it.... Jared "Epinn" wrote: Gord, I found the following link on drop down list and hyperlink without using VBA. Mind you it was Excel 2000. I am not sure about the suggestion and Jared's need, and wonder what you think. One line caught my eyes - "This formula creates immediately a clickable hyperlink." http://www.mrexcel.com/archive/Formulas/19517.html Epinn "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Jared Do you have the ranges named in InsertName Define? You could use event code with the DV list in B1 Option Compare Text Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("B1")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Select Case Target.Value Case "peter" Application.Goto Reference:="peter" Case "paul" Application.Goto Reference:="paul" Case "mary" Application.Goto Reference:="mary" End Select endit: Application.EnableEvents = True End Sub If ranges are not named, just replace Application.Goto Reference:="peter" with Sheets("Sheet2").Range("A1").Select A lot of work if you have a great whack of names. Gord Dibben MS Excel MVP On Tue, 29 Aug 2006 11:22:02 -0700, Jared wrote: Epinn, Okay, the first link got me through making the list i needed, but how do i add a hyper link per name? so clicking the name will get me to a specific cell. but each name needs to be to a different location. i might need to add a event click in vba, but i don't know how to add this to a list. thanks Jared "Epinn" wrote: Jared, Shall I say the second link is a continuation of the first link. If your list of valid entries is in a **different workbook** and you follow the instructions from link 1, you may get an error message "You may not use references to other worksheets or workbooks for Data Validation criteria." I did and I had to do a get around by using the instructions in link 2. It may not be obvious to you until you try it. As I said in my first post, I had to use INDIRECT() which was the second method stated in link 2. Depending on your arrangement, you may or may not need link 2 for the fix. I included it just in case. Please look at link 1 and link 2 as part I and part II. If link 1 doesn't help you at all, you can ignore link 2. You can also forget about link 2, if you use ONE worksheet and not two workbooks. I included the links for your reference as you mentioned drop down list. That's all. Epinn "Jared" wrote in message ... Epinn, I looked through both links, but i couldn't figure out exactly what exactly was i suppose to see in the pages. The first link got me through the list, but the second link i did not understand at all how was connected to my situation thanks "Epinn" wrote: Hi, Since you used the term "drop down list," I would like to highlight the following links. However, the purpose is for data entry/validation. I am not surprised if your approach may have to be something very different - a formula/procedure plus hyperlink? I'll let the experts fill in the blank. http://office.microsoft.com/en-us/as...022151033.aspx http://support.microsoft.com/default...b;en-us;211548 Please note that I had to use INDIRECT() in the case of referencing a **different** workbook (as per the second link above). Thank you for the question and I am sure I can learn something here. Epinn "Jared" wrote in message ... Hi, I have a list of names in worksheet1 I have a Page for each name in Worksheet2 I need to creat a dropdown list in worksheet2 of names in worksheet1 which will allow me to jump to his specific page. how do i do this? Thanks, Jared |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
dropdown list to a page in worksheet
I followed Aladin's suggestion and added IF() to check for a blank cell so
that #N/A! wouldn't be displayed. It works beautifully. It's amazing that one formula does the job. My previous concern about clicking is not necessary. As soon as an item on the drop-down list is clicked upon, a hyperlink (in an adjacent cell) is created instantly, confirming Aladin's statement. Then when I click on the link, I am taken to the appropriate sheet. All in all, it is easier than I have anticipated once the "click" concern is out of the way. Thank you for the question and I have learned something practical. Epinn "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Thanks for the link. Aladin is pretty sharp. Jared could probably use that method. Hope he is still hanging around. Gord On Tue, 29 Aug 2006 17:26:27 -0400, "Epinn" wrote: Gord, I found the following link on drop down list and hyperlink without using VBA. Mind you it was Excel 2000. I am not sure about the suggestion and Jared's need, and wonder what you think. One line caught my eyes - "This formula creates immediately a clickable hyperlink." http://www.mrexcel.com/archive/Formulas/19517.html Epinn "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Jared Do you have the ranges named in InsertName Define? You could use event code with the DV list in B1 Option Compare Text Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("B1")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Select Case Target.Value Case "peter" Application.Goto Reference:="peter" Case "paul" Application.Goto Reference:="paul" Case "mary" Application.Goto Reference:="mary" End Select endit: Application.EnableEvents = True End Sub If ranges are not named, just replace Application.Goto Reference:="peter" with Sheets("Sheet2").Range("A1").Select A lot of work if you have a great whack of names. Gord Dibben MS Excel MVP On Tue, 29 Aug 2006 11:22:02 -0700, Jared wrote: Epinn, Okay, the first link got me through making the list i needed, but how do i add a hyper link per name? so clicking the name will get me to a specific cell. but each name needs to be to a different location. i might need to add a event click in vba, but i don't know how to add this to a list. thanks Jared "Epinn" wrote: Jared, Shall I say the second link is a continuation of the first link. If your list of valid entries is in a **different workbook** and you follow the instructions from link 1, you may get an error message "You may not use references to other worksheets or workbooks for Data Validation criteria." I did and I had to do a get around by using the instructions in link 2. It may not be obvious to you until you try it. As I said in my first post, I had to use INDIRECT() which was the second method stated in link 2. Depending on your arrangement, you may or may not need link 2 for the fix. I included it just in case. Please look at link 1 and link 2 as part I and part II. If link 1 doesn't help you at all, you can ignore link 2. You can also forget about link 2, if you use ONE worksheet and not two workbooks. I included the links for your reference as you mentioned drop down list. That's all. Epinn "Jared" wrote in message ... Epinn, I looked through both links, but i couldn't figure out exactly what exactly was i suppose to see in the pages. The first link got me through the list, but the second link i did not understand at all how was connected to my situation thanks "Epinn" wrote: Hi, Since you used the term "drop down list," I would like to highlight the following links. However, the purpose is for data entry/validation. I am not surprised if your approach may have to be something very different - a formula/procedure plus hyperlink? I'll let the experts fill in the blank. http://office.microsoft.com/en-us/as...022151033.aspx http://support.microsoft.com/default...b;en-us;211548 Please note that I had to use INDIRECT() in the case of referencing a **different** workbook (as per the second link above). Thank you for the question and I am sure I can learn something here. Epinn "Jared" wrote in message ... Hi, I have a list of names in worksheet1 I have a Page for each name in Worksheet2 I need to creat a dropdown list in worksheet2 of names in worksheet1 which will allow me to jump to his specific page. how do i do this? Thanks, Jared Gord Dibben MS Excel MVP |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
dropdown list to a page in worksheet
Epinn,
Where exactly do you add the "if()", in the list cell or the adjecent cell with the hyperlink? Jared "Epinn" wrote: I followed Aladin's suggestion and added IF() to check for a blank cell so that #N/A! wouldn't be displayed. It works beautifully. It's amazing that one formula does the job. My previous concern about clicking is not necessary. As soon as an item on the drop-down list is clicked upon, a hyperlink (in an adjacent cell) is created instantly, confirming Aladin's statement. Then when I click on the link, I am taken to the appropriate sheet. All in all, it is easier than I have anticipated once the "click" concern is out of the way. Thank you for the question and I have learned something practical. Epinn "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Thanks for the link. Aladin is pretty sharp. Jared could probably use that method. Hope he is still hanging around. Gord On Tue, 29 Aug 2006 17:26:27 -0400, "Epinn" wrote: Gord, I found the following link on drop down list and hyperlink without using VBA. Mind you it was Excel 2000. I am not sure about the suggestion and Jared's need, and wonder what you think. One line caught my eyes - "This formula creates immediately a clickable hyperlink." http://www.mrexcel.com/archive/Formulas/19517.html Epinn "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Jared Do you have the ranges named in InsertName Define? You could use event code with the DV list in B1 Option Compare Text Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("B1")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Select Case Target.Value Case "peter" Application.Goto Reference:="peter" Case "paul" Application.Goto Reference:="paul" Case "mary" Application.Goto Reference:="mary" End Select endit: Application.EnableEvents = True End Sub If ranges are not named, just replace Application.Goto Reference:="peter" with Sheets("Sheet2").Range("A1").Select A lot of work if you have a great whack of names. Gord Dibben MS Excel MVP On Tue, 29 Aug 2006 11:22:02 -0700, Jared wrote: Epinn, Okay, the first link got me through making the list i needed, but how do i add a hyper link per name? so clicking the name will get me to a specific cell. but each name needs to be to a different location. i might need to add a event click in vba, but i don't know how to add this to a list. thanks Jared "Epinn" wrote: Jared, Shall I say the second link is a continuation of the first link. If your list of valid entries is in a **different workbook** and you follow the instructions from link 1, you may get an error message "You may not use references to other worksheets or workbooks for Data Validation criteria." I did and I had to do a get around by using the instructions in link 2. It may not be obvious to you until you try it. As I said in my first post, I had to use INDIRECT() which was the second method stated in link 2. Depending on your arrangement, you may or may not need link 2 for the fix. I included it just in case. Please look at link 1 and link 2 as part I and part II. If link 1 doesn't help you at all, you can ignore link 2. You can also forget about link 2, if you use ONE worksheet and not two workbooks. I included the links for your reference as you mentioned drop down list. That's all. Epinn "Jared" wrote in message ... Epinn, I looked through both links, but i couldn't figure out exactly what exactly was i suppose to see in the pages. The first link got me through the list, but the second link i did not understand at all how was connected to my situation thanks "Epinn" wrote: Hi, Since you used the term "drop down list," I would like to highlight the following links. However, the purpose is for data entry/validation. I am not surprised if your approach may have to be something very different - a formula/procedure plus hyperlink? I'll let the experts fill in the blank. http://office.microsoft.com/en-us/as...022151033.aspx http://support.microsoft.com/default...b;en-us;211548 Please note that I had to use INDIRECT() in the case of referencing a **different** workbook (as per the second link above). Thank you for the question and I am sure I can learn something here. Epinn "Jared" wrote in message ... Hi, I have a list of names in worksheet1 I have a Page for each name in Worksheet2 I need to creat a dropdown list in worksheet2 of names in worksheet1 which will allow me to jump to his specific page. how do i do this? Thanks, Jared Gord Dibben MS Excel MVP |
#14
Posted to microsoft.public.excel.newusers
|
|||
|
|||
dropdown list to a page in worksheet
I included IF() as part of the formula (VLOOKUP, HYPERLINK). If you want to
see my formula, please holler back. I have to run off now. Epinn "Jared" wrote in message ... Epinn, Where exactly do you add the "if()", in the list cell or the adjecent cell with the hyperlink? Jared "Epinn" wrote: I followed Aladin's suggestion and added IF() to check for a blank cell so that #N/A! wouldn't be displayed. It works beautifully. It's amazing that one formula does the job. My previous concern about clicking is not necessary. As soon as an item on the drop-down list is clicked upon, a hyperlink (in an adjacent cell) is created instantly, confirming Aladin's statement. Then when I click on the link, I am taken to the appropriate sheet. All in all, it is easier than I have anticipated once the "click" concern is out of the way. Thank you for the question and I have learned something practical. Epinn "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Thanks for the link. Aladin is pretty sharp. Jared could probably use that method. Hope he is still hanging around. Gord On Tue, 29 Aug 2006 17:26:27 -0400, "Epinn" wrote: Gord, I found the following link on drop down list and hyperlink without using VBA. Mind you it was Excel 2000. I am not sure about the suggestion and Jared's need, and wonder what you think. One line caught my eyes - "This formula creates immediately a clickable hyperlink." http://www.mrexcel.com/archive/Formulas/19517.html Epinn "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Jared Do you have the ranges named in InsertName Define? You could use event code with the DV list in B1 Option Compare Text Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("B1")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Select Case Target.Value Case "peter" Application.Goto Reference:="peter" Case "paul" Application.Goto Reference:="paul" Case "mary" Application.Goto Reference:="mary" End Select endit: Application.EnableEvents = True End Sub If ranges are not named, just replace Application.Goto Reference:="peter" with Sheets("Sheet2").Range("A1").Select A lot of work if you have a great whack of names. Gord Dibben MS Excel MVP On Tue, 29 Aug 2006 11:22:02 -0700, Jared wrote: Epinn, Okay, the first link got me through making the list i needed, but how do i add a hyper link per name? so clicking the name will get me to a specific cell. but each name needs to be to a different location. i might need to add a event click in vba, but i don't know how to add this to a list. thanks Jared "Epinn" wrote: Jared, Shall I say the second link is a continuation of the first link. If your list of valid entries is in a **different workbook** and you follow the instructions from link 1, you may get an error message "You may not use references to other worksheets or workbooks for Data Validation criteria." I did and I had to do a get around by using the instructions in link 2. It may not be obvious to you until you try it. As I said in my first post, I had to use INDIRECT() which was the second method stated in link 2. Depending on your arrangement, you may or may not need link 2 for the fix. I included it just in case. Please look at link 1 and link 2 as part I and part II. If link 1 doesn't help you at all, you can ignore link 2. You can also forget about link 2, if you use ONE worksheet and not two workbooks. I included the links for your reference as you mentioned drop down list. That's all. Epinn "Jared" wrote in message ... Epinn, I looked through both links, but i couldn't figure out exactly what exactly was i suppose to see in the pages. The first link got me through the list, but the second link i did not understand at all how was connected to my situation thanks "Epinn" wrote: Hi, Since you used the term "drop down list," I would like to highlight the following links. However, the purpose is for data entry/validation. I am not surprised if your approach may have to be something very different - a formula/procedure plus hyperlink? I'll let the experts fill in the blank. http://office.microsoft.com/en-us/as...022151033.aspx http://support.microsoft.com/default...b;en-us;211548 Please note that I had to use INDIRECT() in the case of referencing a **different** workbook (as per the second link above). Thank you for the question and I am sure I can learn something here. Epinn "Jared" wrote in message ... Hi, I have a list of names in worksheet1 I have a Page for each name in Worksheet2 I need to creat a dropdown list in worksheet2 of names in worksheet1 which will allow me to jump to his specific page. how do i do this? Thanks, Jared Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help...Using dropdown list | Excel Discussion (Misc queries) | |||
want a list of selectable names on my worksheet | Excel Worksheet Functions | |||
Pull list / dropdown data from another worksheet? | Excel Discussion (Misc queries) | |||
Dropdown list key selection | Excel Discussion (Misc queries) | |||
Converting a muliple page worksheet to a single page worksheet | Excel Discussion (Misc queries) |