Multiple Hyperlink from drop down list

[lauren_roberts08]

Hi everyone,

I am an excel beginner and this is my first time posting on this site.
I have no experience with macros, and am only just starting to learn
the ropes of other basic functions.


Currently I have multiple worksheets set up. I want to create a drop
down box in a worksheet that has 5 car dealership names. From this
drop down box i want to be able to click on each one of these names and

have each one be a separate link to a different spot on a different
worksheet.


Specifically:


I have a worksheet called "Brand Names". On this worksheet i want to
create a drop down box that has the name of 5 car dealerships. When I
click on one of these dealerships (say markville chevrolet), i want it
to take me to the section Markville Chevrolet I have created in a
separate worksheet, "Individual Dealer". I need all of the links to go

to this individual dealer sheet, but land at different cell references
within the sheet.


Any help would be greatly appreciated,
THANKS ALOT


lauren

 

[Dave Peterson]

How about an slightly different approach.

I used Data|Validation to create the dropdown.

I added another worksheet and put the names of the dealers in A1:A5 and the
addresses for each dealer in B1:B5.

This is what Sheet2 A1:B5 looked like:
Markville Chevrolet A13
Lauren Cadillac A22
Hundai Excels A31
Smith Bros A40
Johnson and Johnson A49

Then I selected A1:A5 (just the right hand column) and gave it a range name
(Insert|Name|Define). I called it MyList.

Then I went back to the sheet that had the dropdown and selected that cell.

Data|Validation
Allow: List
Source: =myList

In the cell adjacent to that dropdown cell, I put this formula:

=IF(A1="","",HYPERLINK("#"&CELL("address",
INDIRECT("'Individual Dealer'!"&VLOOKUP(A1,Sheet2!A:B,2,0))),"clickme"))

(all one cell)

Now I could use the data|validation to choose the dealership and then click on
the resulting hyperlink.

 

[lauren_roberts08]

Thanks Dave,
When i created a new document to test that it worked, but I'm having a
tough time translating it into my existing document..... it keeps
telling me i have a reference error.

If its not possible to create multiple links directly from the
dropdown, is it possible to click on a cell (say in Sheet1) and when
you click on that cell have it automatically unhide some rows right
above it that contain other information?

I'm going to keep playing with what you gave me before, but if the
above is also an option please let me know- thanks a lot for the help!

 

[Dave Peterson]

If you can't find the reference error, be more specific when you post back--what
step caused the trouble.

You could use a worksheet event that would hide/unhide rows based on the change
of a cell. Include your version of excel when you post back.

I think that the =hyperlink() should work, though.

 

[lauren_roberts08]

Hi Dave,
I am using Excel 2003, and I managed to figure out the reference error
- however the effect isn't exactly what I'm looking for (when i click
the "click me" it brings me to the cell reference, but ideally id like
that reference to be displayed in the top left of the page.

I'm not familiar with worksheet events, how do they work?
And I apologize for multi posting, I assumed each group was separate.

 

[Dave Peterson]

Each newsgroup is separate, but most people who regularly visit these here
newsgroups read most of the newsgroups.

Instead of a worksheet event, how about this.

Show the Forms toolbar.
Drag a button from that toolbar and plop it next to the dropdown.

Assign it this macro.

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim DestCell As Range
Dim res As Variant

'change as necessary
Set myRng = Worksheets("sheet2").Range("MyList")

With ActiveSheet
Set myCell = .Range("a1") '<--change as necessary
If IsEmpty(myCell) Then
Beep
Else
res = Application.Match(myCell.Value, myRng, 0)
If IsError(res) Then
'this shouldn't happen
MsgBox "Design error!"
Else
Set DestCell = Nothing
On Error Resume Next
Set DestCell = Worksheets("Individual Dealer") _
.Range(myRng(res).Offset(0, 1).Value)
On Error GoTo 0

If DestCell Is Nothing Then
'this shouldn't happen either!
MsgBox "Error with lookup table"
Else
Application.Goto DestCell, Scroll:=True
End If
End If
End If
End With

End Sub

You still have to do the creation of the name (MyList).

And you'll have to change this line:
Set myRng = Worksheets("sheet2").Range("MyList")
to point at the correct worksheet (I used Sheet2).

And one more spot.
Change this line:
Set myCell = .Range("a1")
to be the address of the cell with the data|validation|dropdown.

You'll want to read this, too:
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Hi Dave,
I am using Excel 2003, and I managed to figure out the reference error
- however the effect isn't exactly what I'm looking for (when i click
the "click me" it brings me to the cell reference, but ideally id like
that reference to be displayed in the top left of the page.

I'm not familiar with worksheet events, how do they work?
And I apologize for multi posting, I assumed each group was separate.