Activate a hyperlink

[BRISBANEBOB]

I'm reading a hyperlink into a spreadsheet using an =index,match,match formula. The Hyperlink appears as the correct one when you read it in the cell, but it does not link to that address.

A workaround is supposed to be

Select cell
F2
Enter

But that doesn't seem to work either.

Any ideas?

Thanks

[karan]

Why don't you use this macro.

Please Login or Register  to view this content.

Put this macro in a module & then select all the cells you want to hyperlink & then run this macro. It will work for you.

Regards,
Karan

[DonkeyOte]

BrisbaneBob. if you wish to use formulae you need to encase your INDEX/MATCH within the HYPERLINK function, eg:

=HYPERLINK(INDEX(....))

If the INDEX/MATCH is returning a sheet / cell address to a location within the workbook you will need to precede the INDEX with the # icon, eg:

=HYPERLINK("#"&INDEX(...))

[BRISBANEBOB]

The code works brilliantly - thank you.

I would like to use the formula solution but when I try the cell appears as #http//etc and although the hyperlink 'hand' appears when you place the cursor on the cell, if you try to use it as a hyperlink it comes up as an invalid reference (but the reference is valid if you run it using the code solution).

Any ideas where I'm going wrong using the formula solution.

[DonkeyOte]

The # is used specifically as a relative reference marker for references / links to other ranges within the workbook containing the link... if the link is a URL it's not required, ie:

=HYPERLINK(INDEX(....,MATCH(...)))

should work assuming the INDEX/MATCH returns something along the lines of: http://www.google.com

[BRISBANEBOB]

I tried using =hyperlink without # and it takes me to the hyperlink that was previously in the cell! If I then run the code and hit the hyperlink it takes me to correct page.

The hyperlink is being read in along the lines of:-

http://property.companyname.com.au/L...s.asp?pID=4318

[BRISBANEBOB]

Just a thought...

If the code works fine, I should be able to run it automatically when the data in the cell changes. Is that correct, and if so, what code do I use. I tried <change> but I'm either writing it incorrectly or that's not what I should be doing.

Grateful for any help. Thanks

[karan]

If you want to do that, you need to write that code in the Worksheet_Change event & specify the target column & just with a little modification to the previous code, you can achieve that.

Suppose if B is the target column, the code would be this:

Please Login or Register  to view this content.

Write this code in the Sheet Module of the concerned sheet.

Regards,
Karan

[DonkeyOte]

BrisbaneBob, I confess I'm somewhat confused...

In your OP you stated that:

I'm reading a hyperlink into a spreadsheet using an =index,match,match formula

in your penultimate post you then state:

I tried using =hyperlink without # and it takes me to the hyperlink that was previously in the cell!

and I confess I don't understand where in relation to your INDEX/MATCH formula you've placed the HYPERLINK function etc...

Of course if indeed the first quote holds true it's worth pointing out that the VBA Change event will not be appropriate given a cell altering c/o formula does not invoke this event (unless you alter the focus of the Target to be the prime mover in the function) ... you would instead need to use the Calculation event but you will invariably end up processing more cells than is necessary.


For the sake of clarity I've added a very basic example of the approach I was suggesting - based entirely on the premise of the first quote in this post.
If my assumptions are incorrect I would ask you post a sample that reflects the setup of your real file.

[JustABystander]

how to set a destination n this link ? the anchor is sheet1.range("B2:B").value
address is sheet2Range("A2:A").value, is this possbile ?