CODEWITHSUNDEEP
excel
Kenji
Kenji

Reputation: 3

Creating hyperlinks to another worksheet via matching of values

2 worksheets:

  1. List of user who are sitting at a table and each table has a unique ID.
  2. Floor plan with the table ID.

I would like to create hyperlinks for each table ID, when clicked, will direct to the table on the floor plan and highlights the cell.

Table ID (Worksheet 1) search and match table ID (Worksheet 2), return cell position, create hyperlink in Worksheet 1.

Help is appreciated! Thank you.

Upvotes: 0

Views: 919

Answers (1)

Mrig
Mrig

Reputation: 11702

As you mentioned in your comment, Column A of Sheet1 has values 1 to 100 in ascending order.

enter image description here

In Column A of your Sheet2 you have numbers 1 to 100 in random order.

enter image description here

Now, to create the hyperlink, enter the following formula in Cell B1 of Sheet1 and drag/copy it down as required

=HYPERLINK("#'Sheet2'!" & ADDRESS(MATCH($A1, Sheet2!$A$1:$A$100, 0), 1), $A1)

enter image description here

To take care of error when no match is found, use:

=IFERROR(HYPERLINK("#'Sheet2'!" & ADDRESS(MATCH($A1, Sheet2!$A$1:$A$100, 0), 1), $A1),"")

Upvotes: 1

Related Questions