|To do it the way you wish, |
IE: Enter a search string in Sheet 1, Cell A1
and then have excel automatically search Sheet 2 for the string
you will need a Macro.
Unfortunately my Macro skills are just above nil.
But, you can do something similar, using the built in Search & Replace:
Select the worksheets that you want to search by holding down the SHIFT key
and clicking the worksheet tabs.
On the Edit menu, click Find.
In the Find what box, type the data that you are searching for,
and then click Find Next.
You should be brought to the select cell and you can then click on the Hyperlink.
Not knowing how you have your sheets set up, you may have to change the
Search Options, to Values in the drop box labeled "Look In"