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:

FNAME	LNAME	DOB	Number
SANDRA	ABDULALI	01/15/1903	1502
ANTHONY	ABRAMOVA	02/15/1979	4048
PRECIOUS	ABRAMOVE	03/30/1961	5807
GULNARA	ABRAMOVICH	06/01/1959	2126
CARL	ABRAMS	04/29/1998	1333
CYNTHIA	ABUNDS	11/15/1988	991
VERONICA	ACEVED	01/05/1995	1763
JUDITH	ACEVEDO	11/21/1966	5113
ERNEST	ACOSTA	05/24/1969	1336

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Value Tab:

FNAME LNAME DOB SANDRA ABDULALI 01/15/1903 GULNARA ABRAMOVICH 06/01/1959 PRECIOUS ABRAMOVE 03/30/1961 JUDITH ACEVEDO 11/21/1966 ERNEST ACOSTA 05/24/1969 ANTHONY ABRAMOVA 02/15/1979 CYNTHIA ABUNDS 11/15/1988 VERONICA ACEVED 01/05/1995 CARL ABRAMS 04/29/1998 <colgroup><col><col><col></colgroup><tbody> </tbody>

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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!