Dynamic linking of text to another worksheet via a search function

[jtwinte]

Hi there,

I am wanting to hyperlink text from one sheet to search for that corresponding text on another sheet. I've found how to manually link text to a cell on another sheet but is there a way to make this dynamic and incorporate a search function so i do not have to manually carry out the linking of text to its corresponding cell on another sheet?

ie i want to link the text "library" from cell A3 in worksheet 1 to look up the corresponding cell in column A in worksheet 2 with "library" in it.

Thanks

 

[Yongle]

Try this formula in cell B3 (in sheet 1)
- amend sheet name to name of worksheet 2
=HYPERLINK("#"&CELL("address",INDEX('Sheet Name'!A:A,MATCH("*"&A3&"*",'Sheet Name'!A:A,0))),"Jump to "&A3)

the above finds library and hyperlinks to cell A4

 

[jtwinte]

Try this formula in cell B3 (in sheet 1)
- amend sheet name to name of worksheet 2
=HYPERLINK("#"&CELL("address",INDEX('Sheet Name'!A:A,MATCH("*"&A3&"*",'Sheet Name'!A:A,0))),"Jump to "&A3)


View attachment 12895


the above finds library and hyperlinks to cell A4
View attachment 12894

Thanks Yongle this is what I'm after but looking to be able to create the hyperlink in cell A3 from the word 'library' so the B column isnt needed. Is this possible?

 

[Yongle]

Put this formula in cell A3, amending Sheet Name

=HYPERLINK("#"&CELL("address",INDEX('Sheet Name'!A:A,MATCH("*"&"Library"&"*",'Sheet Name'!A:A,0))),"Library")

 

[jtwinte]

Put this formula in cell A3, amending Sheet Name

=HYPERLINK("#"&CELL("address",INDEX('Sheet Name'!A:A,MATCH("*"&"Library"&"*",'Sheet Name'!A:A,0))),"Library")

Thanks Yongle that worked a treat!