Dynamic Hyperlink Based on Cell value

[HolyLemon]

Is there a way to create a hyperlink that is dynamically linked to a cell value, in which the cell value points to a cell reference within a worksheet, and that when the value is changed can point to a different cell reference.

I am looking for a way to create a hyperlink without using Macros. I have a column (P) in a worksheet called Annual Review. Everything in column P is an automaticly generated Row number referencing the customers details on the other worksheet "MGP4" So if in P2 on "Annual Review" it says 43. I know that the Customers Details can be found on line 43 on worksheet "MGP4".

Im looking for a way to make it a hyperlink but as a formula so i can copy it down, there are several thousand entries on both worksheets.

If anyone can help me or has any questions i would be very grateful.

Thanks,
Harry

 

[Andrew Poulsom]

Like this?

=HYPERLINK("#Sheet2!B"&MATCH(A1,Sheet2!B:B,FALSE))

 

[vikas29]

Not able to use it. Can you please cross check? Also would like to understand where are we comparing Column A of Sheet1 with Column B of Sheet2 here?

 

[Andrew Poulsom]

This:

MATCH(A1,Sheet2!B:B,FALSE)

returns the row number on Sheet2 where the value in A1 appears in column B. I checked the formula before posting it.

 

[vikas29]

This:

MATCH(A1,Sheet2!B:B,FALSE)

returns the row number on Sheet2 where the value in A1 appears in column B. I checked the formula before posting it.

Thanks a lot Andrew. Worked perfectly. Earlier I was putting the formula in wrong sheet.

 

[RAQUELAMIGARAKO]

Hi there urgent help, is there a way to make an automatic hyperlink???
Lets say on a column i Want to hyperlink very cell thats contains the name "juan", so overtime the word "juan" appears it redirects me to a specific document...

 

[•Daniel•]

This:

MATCH(A1,Sheet2!B:B,FALSE)

returns the row number on Sheet2 where the value in A1 appears in column B. I checked the formula before posting it.

How.about.dynamic.column?

 

[sidetracked350]

Ok, This works great. I use it to link a cell value into search bars of online retail sites, but I've hit some snags with a few. I usually type a search into their box then copy/edit the url into the formula on my sheet. This works fine for most but on some sites I get caught with searchers like .com/?target=search&mode which I have yet to find a workable edit that can bypass the original keyword I used with the cell value. Other ones I get stuck on are the sites that are encoded as such .com/search.php?encode=YTo0OntzOjg6ImtleXdvcmRzIjtzOjY6Im or sites that perform their searches in secondary pop-up windows. Would anyone here know how to solve any, if not all of these little hang-ups? I would like to do this without the use of VB if possible for now. The method I am currently using is =hyperlink("website.com/?search="&$B$3&","Site Name") and it works fine for most except for a few as mentioned.
Here are a few of the links I am having trouble with for example.

Golden Lagoon Collector | Lowest priced, New Transformers 3rd Party MP
Agabyss.com
x2oToys - Malaysia No 1 3rd Party Transformers Shop

Thanks in advance for your time and any help you can offer.

-Gus

 

[skf786]

Hi,

a related question. On pressing the hyperlink, I would like it to goto cell E5 on another sheet named 'MGP4' and input value given in P2 in that cell i.e. E5. Also, I would like numerous such hyperlinks in the present sheet.....pressing any of them will lead to the value being input in cell E5 of MGP4.

thanks in advance!

 

[skf786]

Hi Ian,

is it possible that when the hyperlink is pressed, it goes to the destination cell and enters a specific text in a desired cell...say P5. The desired text is already entered in cell B4.

thanks in advance.

best regards,

Khalid

 

[skf786]

Hi,

On pressing the hyperlink, I would like to go to cell E5 on another sheet named 'MGP4' and input value given in P2 in that cell i.e. E5. Also, I would like numerous such hyperlinks in the file.....pressing any of them will lead to the value being input in cell E5 of MGP4.

thanks in advance!