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).
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.
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
What you are looking for is a dynamic hyperlink.
So let's say you have
- team members with (column
- command leads and (column
- reference / project numbers (column
we have a table with different information and the same reference / project number in a column
Then you can add a new column
to the table on
, which you can name
, 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:
source to share