Dynamic hyperlinks generated using ID

[Tom A]

Hi,

I've got two worksheets called venue and note in Excel 2013. I've created dynamic hyperlinks in the note worksheet to refer to venues in the venue worksheet by using the hyperlink function + index and match + cell functions.

In the note worksheet, you have to enter the venue ID and it generates the hyperlink to the venue in the Venue worksheet by looking up it's name using the Venue ID value and then I get the cell reference of the Venue name cell in the Venue worksheet to generate the dynamic hyperlink.

However, the hyperlinks are only working for venue ID's from 1 to 15 even though I have venues with Venue ID's higher than 15 in the Venue worksheet.

This is some sample data I am using in the note worksheet.
The venue ID is in cell j2, VenueName is in m2, Venue name cell reference is in cell l2 and the hyperlink is in cell k2:

VenueID
18

VenueName
=INDEX(Venue_Name,MATCH(J2,Venue_ID,0))

Venue_Name_CellReference
=CELL("address",INDEX(Venue_Name,MATCH(J2,Venue_ID,0)))

VenueLink
=HYPERLINK(L2,M2)

Thanks,
Tom

 

[NARAYANK991]

Hi ,

I have no idea how IDs 1 through 15 work ; I have tried out the formulae posted by you , and the following HYPERLINK formula works :

=HYPERLINK("#" & "venue!" & L2,M2)

where L2 contains the formula posted by you , and resolves to a cell address ; M2 contains the friendly name.

Narayan

 

[Tom A]

The venue ID's are numbers going from 1 to 2990. I'm still having the same issue. I've noticed it's not picking up venue ID 14 either even though that has a venue name but it picks up venue ID 15 and then none of the rest going up to 23. I get a '#n/a' error for all three formulas when this happens.
I've checked the named ranges and they seem to be fine to me.

These are the named ranges I am using:

Venue_ID
=Venue!$C$2:$C$3000


Venue_Name
=Venue!$D$2:$D$3000

 

[Haseeb A]

Hello Tom,

I think, you may have cells formatted differently in J2 & Venue!$C$2:$C$3000. All these cells must be either Text or Number.

Try this version & see if this one works or not,

=HYPERLINK("#'Venue'!D"&MATCH(J2&"",INDEX(Venue_ID&"",),0)+1,INDEX(Venue_Name,MATCH(J2&"",INDEX(Venue_ID&"",),0)))

 

[Tom A]

Hi Haseeb! Changing the format of the cells didn't work but That formula appears to be working! Many thanks for your help

Tom

Hello Tom,

I think, you may have cells formatted differently in J2 & Venue!$C$2:$C$3000. All these cells must be either Text or Number.

Try this version & see if this one works or not,

=HYPERLINK("#'Venue'!D"&MATCH(J2&"",INDEX(Venue_ID&"",),0)+1,INDEX(Venue_Name,MATCH(J2&"",INDEX(Venue_ID&"",),0)))