0

Example - 'Main'!A2 - Nested if checking the value of 'main'!a1, return the value from 'data'!A2 (if it's contains a hyperlink-return the hyperlink).

Here is what I have:

=IF($E$2=1,HYPERLINK('Non Brand'!B3),IF($E$2=2,HYPERLINK(Agile!B3),IF($E$2=3,HYPERLINK(Infra!B3),IF($E$2=4,HYPERLINK('BDO Only'!B3),IF($E$2=5,HYPERLINK('IT Only'!B3)," ")))))

If E2=1, return the value of Non Brand B3 (if it is a hyperlink, return the hyperlink not just the text; else if E2=2 return the value of Agile B3 (if it is a hyperlink, return the hyperlink not just the text; else if E2=3-----rinse and repeat.

B3 - Some cells contain just text of the name of the document; others contain a hyperlink to the document template to use.

Every return value whether it is a hyperlink or not is returned as a hyperlink. The hyperlinks that are valid are giving an error that site cannot be reached (even though they are internal company links and I'm on our network).

If E2 is not =1, it returns FALSE, it doesn't continue with the nested if to check if it =2, etc...

Improve this question
4
  • I am confused about what your question is. How does your code not work? What does it do or not do?
    – Rohit Gupta
    Commented Oct 3, 2023 at 22:20
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Commented Oct 3, 2023 at 22:20
  • If CMD!E2 equals 1... I want CMD!A5 to return whatever is in NonBrand!A5 (It contains a hyperlink to a document template). Else check if CMD!E2 equals 2... I want CMD!A5 to return Agile!A5 (It contains a hyperlink to a document template) else....etc. through CMD!E2 equals 6.... If nonBrand!A5 doesn't contain a hyperlink just return the text. the hyperlink that it returns gives an error when clicked on that the site cannot be opened. It is internal to our co. The cells that do not contain hyperlinks are returning hyperlinks that obviously don't work not just the text in the cell.
    – Rita
    Commented Oct 3, 2023 at 22:43
  • Prior to adding the HYPERLINK portion into the formula, the nested if worked but it did not return the hyperlink, just the text in the cell.
    – Rita
    Commented Oct 3, 2023 at 23:05

1 Answer 1

Reset to default
0

What you see in a cell with a hyperlink is normally not the hyperlink address, which is necessary for HYPERLINK function. There is no such function in Excel which reads this address. You should use VBA and define UDF as such (in VBA standard module):

Function AdrHyper(r As Range) As String
   If r.Hyperlinks.Count > 0 Then
      AdrHyper = r.Hyperlinks(1).Address
   End If
End Function

Then you can use spreadsheet functions

=IFERROR(CHOOSE($E$2,HYPERLINK(AdrHyper('Non Brand'!B3),'Non Brand'!B3),HYPERLINK(AdrHyper(Agile!B3),Agile!B3)),"")

In your case it's better to use CHOOSE function then nested IF's. You can add more addresses if you need, but each address should be repeated twice as in the above example.
As the file contains UDF function it must be saved in .xlsm format.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .