CODEWITHSUNDEEP
vbaexcelhyperlink
user3017248
user3017248

Reputation: 11

Turn a cell into a hyperlink if there is a URL

I have a sheet in excel that contains names and alongside the name for "some" of the names is a URL.

The name is then used in another sheet and may follow on into other cells.

Basically, it is a pool tournament and winners go through to later rounds in the tournament and what I want to happen is that, if there is a URL in the sheet to turn the instances of their name following on into hyperlinks.

I am aware of the vlookup, HYPERLINK() and if's etc but linking them all together and it actually working is hurting my brain!

The cell D68 currently contains:

=IF(E4<>"",IF(E4<F4,G4,D4),L4&" - "&M4)

Sheet One

Sheet Two - Second instance of the name

Upvotes: 0

Views: 166

Answers (1)

Sam Gilbert
Sam Gilbert

Reputation: 1702

It wasn't clear from your question where you want to place your formula, so I have assumed that you want to add the formula to the sheet in picture eG2fj.png.

I would recommend to split this formula into parts to make it easier to deal with. I believe your formula should be made up of the following:

=IF(use VLOOKUP to see if user has an associated link, "Link exists so show name as hyperlink", "Link doesnt exist so just show the name")

Now build the parts individually, first VLOOKUP

=VLOOKUP(D68, 'C2mp6'!$A$2:$B$7, 2, 0)

This looks up the name in cell D68 in 'C2mp6'!$A$2:$B$7 and returns the value in the second column if there is an exact match.

For the first part of the formula we just need to test if this is empty or not. I.e. if a link has been provided or not. Below will return TRUE if a link is provided, FALSE if a link wasnt provided:

=VLOOKUP(D68, 'C2mp6'!$A$2:$B$7, 2, 0) <> ""

Now add this back to the full formula:

=IF(VLOOKUP(D68, 'C2mp6'!$A$2:$B$7, 2, 0) <> "", "Link exists so show name as hyperlink", "Link doesn't exist so just show the name")

Now to deal with situation if link exists, i.e. TRUE. To do this will use HYPERLINK. First test on it's own. First argument is the VLOOKUP from before (i.e. the link, as this is TRUE we know the link is populated), second part is how the link will be displayed, i.e. the players name:

=HYPERLINK(VLOOKUP(D68, 'C2mp6'!$A$2:$B$7, 2, 0), D68)

Once this is working, add to the full formula:

=IF(VLOOKUP(D68, 'C2mp6'!$A$2:$B$7, 2, 0) <> "", HYPERLINK(VLOOKUP(D68, 'C2mp6'!$A$2:$B$7, 2, 0), D68), "Link doesn't exist so just show the name")

Finally cover off what happens if the link doesn't exist. Although you might think that you can just add in a cell reference to the players name, this actually causes an issue as excel still thinks it is a hyperlink. So instead we need to add in a "dummy" hyperlink that say just references the players name. For example:

=HYPERLINK("#"&CELL("address", D68), D68)

Finally add this altogether:

=IF(VLOOKUP(D68, 'C2mp6'!$A$2:$B$7, 2, 0) <> "", HYPERLINK(VLOOKUP(D68, 'C2mp6'!$A$2:$B$7, 2, 0), D68), HYPERLINK("#"&CELL("address", D68), D68))

Hope this helps

Upvotes: 2

Related Questions