Hyperlinks and drop-down lists
Posted by Connie on June 08, 2001 1:21 PM
Here's what I'm trying to accomplish (without using VBA):
I've got a drop-down cell with customer names in it (just made from data validation>list). I'd like for the user to choose the customer from this list, then be able to click on a hyperlink and go to that customer's page (which is a separate worksheet within the same workbook)and read about them. The problem I run into is I've got about 30 customers, and I can only use 7 "IF" statements (i.e.=IF(a1="Smith",HYPERLINK'Smith'!a1,IF(a1="Jones",HYPERLINK'Jones'!a1) etc. I was trying to avoid having 30 different links on the page.
Thanks,
Connie
Posted by Aladin Akyurek on June 08, 2001 1:39 PM
Connie
Please use VLOOKUP for that.
Make a 2-column table Customer X Hyperlink
Feed the value selected from the dropdown box to a VLOOKUP formula:
=VLOOKUP(selected-customer,HYPERLINKS,2,0)
HYPERLINKS is a name that you can give to the 2-column table, suggested above. Watch out for one thing though: Customer names must be uniques. If that is not the case, use a system of uniques ids for customers.
Aladin
Posted by Connie on June 08, 2001 2:09 PM
Aladin,
I'm unclear what I put in the Hyperlink column. I don't actually put the clickable hyperlink there, do I? I tried that and got #N/A in the cell where I have the Lookup formula. Also, what does the "0" at the end of the lookup formula denote?
Thanks for your help,
Connie
Posted by Aladin Akyurek on June 08, 2001 3:16 PM
Connie
I almost regretted my proposal. I tried out the following:
Blackboard [UITW Woonlasten 00-01.xls]Blackboard!A1
This is just an example that consists of a 1 x 2 table. The value in column 2 is exactly a link reference (not a true hyperlink -- rather a spec for a hyperlink). You type this yourself, which consists of the filename, sheetname, and the cell where you want to be.
When you select Blackboard from a dropdown box located say in A1, enter in say C1 the formula:
=HYPERLINK(VLOOKUP(A1,HYPERLINKS,2,0))
where HYPERLINKS is the name of the lookup table.
The '0' as the 4th arg of VLOOKUP means just FALSE. This value forces VLOOKUP to do an exact match.
This formula creates immediately a clickable hyperlink.
Wow, I've learned something practical from this.
Note. I cooked up this in Excel 2000.
Aladin
Posted by Connie on June 11, 2001 6:31 AM
Thanks, Aladin! It works perfectly! Great solution!! (NT)
I almost regretted my proposal. I tried out the following: Blackboard [UITW Woonlasten 00-01.xls]Blackboard!A1 This is just an example that consists of a 1 x 2 table. The value in column 2 is exactly a link reference (not a true hyperlink -- rather a spec for a hyperlink). You type this yourself, which consists of the filename, sheetname, and the cell where you want to be. When you select Blackboard from a dropdown box located say in A1, enter in say C1 the formula: =HYPERLINK(VLOOKUP(A1,HYPERLINKS,2,0)) where HYPERLINKS is the name of the lookup table. The '0' as the 4th arg of VLOOKUP means just FALSE. This value forces VLOOKUP to do an exact match. This formula creates immediately a clickable hyperlink. Wow, I've learned something practical from this. Note. I cooked up this in Excel 2000.
