Excel Daily News

Hyperlinks and drop-down lists

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.

(6 responses) I'm part of an organisation that is altering the way it manages a waiting list, and we have decided we want a way of picking n people per ye

Let's Connect

MrExcel.com debuted on November 21, 1998.

MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures
for illustration only, without warranty either expressed or implied, including
but not limited to the implied warranties of merchantability and/or fitness for
a particular purpose. The Formulas, Functions and Visual Basic procedures on this
web site are provided "as is" and we do not guarantee that they can be used in all
situations.
This site contains affiliate links. Any affiliate commissions that we
earn when you click a link to Amazon or other sites is reinvested in keeping MrExcel.com
running. You can earn a commission for sales leads that you send to us by joining our
affiliate program.
View my Privacy Policy and Terms of Use.