Link between two dynamic points

[lindseya66]

Hello,

I am having trouble with creating a hyperlink between two dynamic points.

Sheet 2 is updated with new data every night.

Sheet 1 is a smaller subset of the data, that also gets updated every night based on the data from sheet 2. The idea is to click on the project id on sheet 1 and it will take you to the project ID on sheet 2.

Any help would be appreciated!!

 

[jkpieterse]

Suppose your other table is on Sheet2. This formula in an empty column takes you to the first cell containing that product ID:

=HYPERLINK("#"&CELL("address",INDEX(XLOOKUP(A2,Sheet2!$A$2:$A$13,Sheet2!$A$2:$A$13),1)),"Link to " &A2 )

 

[lindseya66]

Suppose your other table is on Sheet2. This formula in an empty column takes you to the first cell containing that product ID:

=HYPERLINK("#"&CELL("address",INDEX(XLOOKUP(A2,Sheet2!$A$2:$A$13,Sheet2!$A$2:$A$13),1)),"Link to " &A2 )

Thank you! I will give it a try.

 

[lindseya66]

=HYPERLINK("#"&CELL("address", INDEX(XLOOKUP(A2,Sheet2!$A$2:$A$13, Sheet2!$A$2:$A$13),1)), "Link to" &A2)

This is what I typed in. It returns N/A# error.

When I do the evaluate calculation it is saying that there is an issue with the XLookUp part of the calc. Any advise?? Please and thank you!!

 

[jkpieterse]

It means XLOOKUP does not find a matching ID in the other worksheet.
Try inserting just the XLOOKUP bit to ease trouble-shooting.
In addition: make sure the second and third argument of XLOOKUP point to all data in the other sheet and point to the same cells.

 

[lindseya66]

thank you!

 

[lindseya66]

I finally got it to work! thank you!