If = Hyperlink to row

[nawam]

Hi Folks,
These seems like it should be simple, but it's hanging me up.

I want to hyper link data in one tab (Data) to the data in another tab (values) if certain criteria are met.

For example, the Data tab will have names in Column A, date of birth in column b and member number in column c.

The Values tab will have this same data.

What I want is on the values tab I'd like a hyper link to the row of corresponding information in the data tab if all three criteria match.

I'm thinking a fairly simple nested formula or something should work out, but I'd roll a macro if I need to.

Thanks in advance.

 

[p45cal]

It's quite a simple question but there may be several ways to do it and varying levels of difficulty depending on how easy it is to determine the corresponding row (it'd be more difficult if there were say vlookup formulae involved as it would be more difficult to find where the data came from). Do you want this/these hyperlinks to be in the same cell(s) as the value(s), or perhaps in a separate cell next to that value(s)?
There are two ways to get a hyperlink, one is with a right-click, then choose Hyperlink…, the other is to use the =HYPERLINK() worksheet function. The former would be easier to create/maintain with a macro if there were many cells involved, the latter could also be dealt with by a macro, but a pure worksheet formula solution might be possible.

I'm thinking it might answer lots of questions without doubts if you were able to prepare a version of your workbook, put it onto the interweb somewhere and link to it here.

 

[p45cal]

duplicate post (it said I had to wait before posting again - but I had only presed the submit button once) This site is getting impossibly difficult.

 

[nawam]

Thanks for your prompt reply

Data Tab:
[TABLE="width: 354"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]FNAME[/TD]
[TD]LNAME[/TD]
[TD]DOB[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]SANDRA[/TD]
[TD]ABDULALI[/TD]
[TD="align: right"]01/15/1903[/TD]
[TD="align: right"]1502[/TD]
[/TR]
[TR]
[TD]ANTHONY[/TD]
[TD]ABRAMOVA[/TD]
[TD="align: right"]02/15/1979[/TD]
[TD="align: right"]4048[/TD]
[/TR]
[TR]
[TD]PRECIOUS[/TD]
[TD]ABRAMOVE[/TD]
[TD="align: right"]03/30/1961[/TD]
[TD="align: right"]5807[/TD]
[/TR]
[TR]
[TD]GULNARA[/TD]
[TD]ABRAMOVICH[/TD]
[TD="align: right"]06/01/1959[/TD]
[TD="align: right"]2126[/TD]
[/TR]
[TR]
[TD]CARL[/TD]
[TD]ABRAMS[/TD]
[TD="align: right"]04/29/1998[/TD]
[TD="align: right"]1333[/TD]
[/TR]
[TR]
[TD]CYNTHIA[/TD]
[TD]ABUNDS[/TD]
[TD="align: right"]11/15/1988[/TD]
[TD="align: right"]991[/TD]
[/TR]
[TR]
[TD]VERONICA[/TD]
[TD]ACEVED[/TD]
[TD="align: right"]01/05/1995[/TD]
[TD="align: right"]1763[/TD]
[/TR]
[TR]
[TD]JUDITH[/TD]
[TD]ACEVEDO[/TD]
[TD="align: right"]11/21/1966[/TD]
[TD="align: right"]5113[/TD]
[/TR]
[TR]
[TD]ERNEST[/TD]
[TD]ACOSTA[/TD]
[TD="align: right"]05/24/1969[/TD]
[TD="align: right"]1336[/TD]
[/TR]
</tbody>[/TABLE]


Value Tab:

[TABLE="width: 299"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 241"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]FNAME[/TD]
[TD]LNAME[/TD]
[TD]DOB[/TD]
[/TR]
[TR]
[TD]SANDRA[/TD]
[TD]ABDULALI[/TD]
[TD="align: right"]01/15/1903[/TD]
[/TR]
[TR]
[TD]GULNARA[/TD]
[TD]ABRAMOVICH[/TD]
[TD="align: right"]06/01/1959[/TD]
[/TR]
[TR]
[TD]PRECIOUS[/TD]
[TD]ABRAMOVE[/TD]
[TD="align: right"]03/30/1961[/TD]
[/TR]
[TR]
[TD]JUDITH[/TD]
[TD]ACEVEDO[/TD]
[TD="align: right"]11/21/1966[/TD]
[/TR]
[TR]
[TD]ERNEST[/TD]
[TD]ACOSTA[/TD]
[TD="align: right"]05/24/1969[/TD]
[/TR]
[TR]
[TD]ANTHONY[/TD]
[TD]ABRAMOVA[/TD]
[TD="align: right"]02/15/1979[/TD]
[/TR]
[TR]
[TD]CYNTHIA[/TD]
[TD]ABUNDS[/TD]
[TD="align: right"]11/15/1988[/TD]
[/TR]
[TR]
[TD]VERONICA[/TD]
[TD]ACEVED[/TD]
[TD="align: right"]01/05/1995[/TD]
[/TR]
[TR]
[TD]CARL[/TD]
[TD]ABRAMS[/TD]
[TD="align: right"]04/29/1998[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I'd like to Hyperlink to, lets say the last name, in the Data tab if the data in A,B,and C on the Value tab are present in any one row on the data tab. Does that make sense?

I've worked a good bit with =Hyperlink in the past but it's been linking to tabs and such, I've never linked to a specified cell before. My formula work simply isn't where it used to be. I was thinking maybe an index, match, hyperlink combination. But honestly I'm stumped here for some reason.

Thanks again.

 

[p45cal]

download this file:https://www.box.com/s/nmo0l4g7k2scjs721tz9
Click on one of the words 'link' in column D of the Value tab.
The Data tab name must not have a space in it.

The formulae I have in the cells is like:

=IFERROR(HYPERLINK(SUBSTITUTE(CELL("address",OFFSET(Data!$D$1,MATCH(A2&B2&C2,Data!$A$2:$A$10 & Data!$B$2:$B$10 & Data!$C$2:$C$10,0),0)),"'",""),"link"),"not found")

Unfortunately, I didn't get around to trying to put it into a cell with data already in.

 

[nawam]

I think there's a good chance that you are a rockstar!
Let me try to cram it into a couple thousand rows of data and we'll see. I'll let you know later today how it goes, but it looks good based on your model.

Thanks

 

[nawam]

Okay, This is perfect. It's exactly what I needed. And, on top of all of that, it makes sense. You're awesome.

Thanks Again!

 

[p45cal]

Jolly good!