Dynamic hyperlink to a cell based on the value of another cell

[Chunkypar]

Hi
Hope you can help.

I have a workbook set up to track service requests raised by our team with an input sheet and a data sheet.

The requests are tracked by a specific reference number, and on the input sheet I have a search field set up where the user can type this in and 'enquire' to bring up some basic details on the service request.

What I would like to do is create a hyperlink button so that the user can then navigate directly to the full service request record on the data sheet. This hperlink would need to be dynamic so that it navigates to the correct line on the data sheet based on the value input into the search field on the input sheet.

Any help would be greatly appreciated

Thanks

[Domski]

Hi,

I don't think you will be able to do this with a hyperlink but you could have a button on the worksheet that kicked off a macro to perform the search if that would be any good.

Dom

[Chunkypar]

It certainly would!

I've very little experience with VBA though so not sure how easy it would be to put together?

[Domski]

This would look for the value in A1 on Sheet1 in column A on Sheet2 and select it if found:

Sub test()

Dim rngFindRange As Range
Dim strRefNum As String

strRefNum = Sheets("Sheet1").Range("A1")

Set rngFindRange = Sheets("Sheet2").Range("A:A").Find(strRefNum, LookIn:=xlValues, LookAt:=xlWhole)

If rngFindRange Is Nothing Then
    MsgBox strRefNum & " not found.", vbExclamation, "Reference Number No Found"
Else
    Sheets("Sheet2").Activate
    rngFindRange.Select
End If

End Sub

If you need any more info let me know.

Dom

[Chunkypar]

Works like a dream!

Thanks for your help

[Whizbang]

Does this do as requested?

It uses the formula:
=HYPERLINK(CELL("address",INDEX(ReferenceNums,MATCH($F$2,ReferenceNums,0))),"Go to Record")

Where $F$2 is the input/search cell.

[Whizbang]

Update on this.

Once the file is saved, the CELL() function inserts some single quotes in the workbook name, which makes HYPERLINK fail.

Use this formula:
=HYPERLINK(SUBSTITUTE(CELL("address",INDEX(ReferenceNums,MATCH($F$2,ReferenceNums,0))),"'",""),"Go to Record")

[Whizbang]

Further update on this

Evidently the # symbol in the HYPERLINK function means "this workbook".

So this...

=HYPERLINK("#Sheet1!A" & MATCH($F$2,A:A,0), "Go To Record")

... should work just fine. Awesome!

Thanks to NBVC in this post for this little bit of knowledge.

[littleironfish]

It didn't work for me ... I had to change to be as follow:

=HYPERLINK("#Sheet1!A" & MATCH(F2,Sheet1!A:A,0), "Go To Record")

[jasonpgarner]

Hi ppl,

I am stuck, trying to make a dynamic hyperlink based on a date in A1 that is a drop down cell. the corresponding date will be somewhere within an array (A5:A:A0300) but the formula wont work. I can make it work for one column but not an array. I have tried these two variations. Not a real genius at this, any help is appreciated

=HYPERLINK("#INDEX(A:AO,MATCH("&A1&",A:AO,1))","Link")
=HYPERLINK("#Index!(A5:AO300 & MATCH($A$1,A5:A:A0300,0),1)","GoToRecord")

[FDibbins]

Jason, welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.