Automatically reference a cell in another sheet if the value is the same

I have a status update sheet (sheet1) where the team members have a row per action and each action has a reference number (s1, columnA).

enter image description here

In another sheet (sheet 2) I have commands that write their own status updates, but may not necessarily refer to each reference number, and they will not do so in the same order as for sheet1.

enter image description here

Basically what I'm looking for is a column in sheet1 to show the link automatically if the reference number in column A of sheet1 is also somewhere in column B in sheet2.

Is it possible? If so, how do I do it? I hope to use a formula, but I'm not sure if this is possible. Hopefully use VBA as a last resort as it will be a generic spreadsheet and the chances of someone messing it up are decent.

Thanks, and please let me know if I can clarify anything.


source to share

1 answer

What you are looking for is a dynamic hyperlink.

So let's say you have Sheet1


  • team members with (column B

  • command leads and (column A

  • reference / project numbers (column C


In Sheet2

we have a table with different information and the same reference / project number in a column E


Then you can add a new column D

to the table on Sheet1

, which you can name Links

, and the formula here should be:

=HYPERLINK("#Sheet2!E"&MATCH(D2,Sheet2!E:E,0),"Link to "&D2)


If you are using tables with named columns you can also use:

=HYPERLINK("#Sheet2!E"&MATCH([@[Reference Number]],Sheet2!E:E,0),"Link to "&[@[Reference Number]])


Of course, you can adjust the alt text to something more appropriate.

Perhaps the following image will help explain the following:

enter image description here



All Articles