excel - Automatically link to a cell in another sheet if value is the same

Question

I have a status update sheet (sheet1) in which team members have a line per action, and each action has a reference number (s1, columnA).

In another sheet (sheet2), I have the team leads write their own status updates, but they may not necessarily reference every single reference number, nor will they do it in the same order as sheet1.

Basically, what I'm looking for is a column in sheet1 to show a link automatically IF the reference number in columnA sheet1 is also found somewhere in columnB in sheet2.

Is this possible? If so, how would I work this? I'm hoping to use a formula, but I'm not sure if that's possible. Hoping to use VBA as a last resort since this will be a shared spreadsheet and the probability that someone might screw it up is decent.

Thank you, and please let me know if I should clarify anything.

Answer 1

What you are looking for is a dynamic hyperlink.

So, let's assume that you have on Sheet1 all the

• team members with the (column B)
• team leads and the (column A)
• reference / project numbers (column C).

On Sheet2 we have a table with various information and the same reference / project number in column E.

Then you can add a new column D to the table on Sheet1 which you might want to 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 that you can also use:

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

_{Of course you can adjust the alternative text to something more suitable.}

Maybe the following picture helps explaining the above: