hyperlink to specific cell/row

[trav]

I have several reports that are generated and spit into excel after a
quick macro they wind up in the same workgroup on three spreadsheets.
basically one spreadsheet has the main list with short info, and the
other two sheets have the detailed info. I will probably end up
combining the info of the two sheets into one, they are both exactly
the same but for different customers.

basically i was wondering if there is a way that i can create a
hyperlink that when the user clicks on the order number in the short
info page it takes him to that order number on the detail info page.

i know you can create hyperlinks to certain worksheets but is there a
way to take a user to a specific cell or row.


i hope that isn't too confusing

 

[davesexcel]

=HYPERLINK("#Sheet3!D1",Sheet3!D1)
this will take you to sheet3 D1 and will also carry the value of the
same Cell

 

[trav]

is there a way to combine a look up function so that they hyperlink is
dynamic

sheet 1
ORD095786
ORD095872
ORD095968
ORD096312

sheet 2
ORD095786
ORD095796
ORD095852
ORD095872
ORD095899
ORD095968
ORD096312

so if those are column A for both sheets, is there a formula that i can
insert that will check the Sheet one order number to see its place on
sheet 2 and then create a hyperlink to that cell, or row

 

[Dave Peterson]

How about an alternative?

Use a macro that searches sheet2 column A and if it finds a match, it goes
there.

I'd drop a button from the forms toolbar in Row 1 of sheet1--and make row 1
always visible (window|freeze panes)

Then assign this macro to that button. (The code depends on where the
activecell is.)

Option Explicit
Sub testme01()
Dim myRng As Range
Dim myCell As Range
Dim res As Variant

With Worksheets("sheet2")
Set myRng = .Range("a:a")
End With

Set myCell = ActiveCell.EntireRow.Cells(1)

res = Application.Match(myCell.Value, myRng, 0)

If IsError(res) Then
Beep
Else
Application.Goto myRng(res), scroll:=True
End If

End Sub

 

[trav]

I can't get it to work, it is exactly what i want to do though, so thank
you, but for some reason i can't get it to work.

I first put it into my personal macro workbook, then tried in the sheet
code.

either way i can't get it to work

i get an error 2042
for this line
res = Application.Match(myCell.Value, myRng, 0)

res = error 2042

any suggestions,
i have tried renaming the sheets, no luck

i am kinda baffled.

 

[Dave Peterson]

That means that there wasn't a match.

The error checking lines should have given you a beep.