View original (French)

IF function and Hyperlink

Solved
Gpa2Bol -  
Hello,

For some time now, I've been stuck on combining "IF function and Hyperlink" in Excel.

What I want to do is access the desired cell (A186) in my sheet directly so that I don’t have to navigate through this same sheet which is quite loaded with information. However, when I enter the formula, it seems correct but doesn't lead anywhere. The hyperlink is active, but when I click on it, nothing happens.

To better visualize, I am leaving a copy of my formula.

=IF(L4="Input mentioned in % IDV Monthly", HYPERLINK('[Renseignement_IDV_2013]Renseignement IDV 2013'!A186), "")

Another piece of information, the link should take me to this same sheet without changing the file. I just want to access this cell A186.

If anyone has any information, I would be grateful, and I thank any person who might have the answer in advance.

6 answers

  1.  via55
    Hello

    the hyperlink must be placed in quotes

    HYPERLINK("[workbook name]sheet name!celladdress")

    Regards

    --
    "Imagination is more important than knowledge." A. Einstein
    0
    1. pilas31 Contributor
      Hello,

      I think the link text needs to be modified as follows:
      =IF(L4="Input mentioned in % IDV Monthly",HYPERLINK("[Renseignement_IDV_2013.xlsx]'Renseignement IDV 2013'!A186");"")

      Put double quotes around the entire link text
      Use single quotes for the sheet name because there are spaces
      Add the file extension

      To be tested
      Best regards,
      0
      1. via55
        Hello pilas

        No, no need for the file extension, but we agree on the quotes

        Best regards
        0
      2. Gpa2Bol
        Hello everyone,

        First of all, I want to thank you for your precious help.
        However, either I understand nothing, in which case I should be worried :-), or my Excel is buggy?

        I've typed the different formulas you provided, but none of them work.
        So, I'm leaving you my email address and I've set aside a draft of my workbook in case the problem interests you and if I'm not bothering you.

        Thank you in advance.
        0
      3. via55
        Good evening

        No email address on the site, but you can post an example of your lightweight file on cjoint.com and then provide the link here

        Best regards
        0
    2. Gpa2Bol
      Good evening and thank you to via55 for your help.

      As requested, I am providing the link to my simplified file.
      A little additional information, the formula I want to obtain will only be the launch of my "if function + hyperlink" but I need this functional start in order to understand the mechanism to reproduce it later. As a reminder, the target cell is A186.
      (concerned part in blue).

      Here are the tested formulas that did not work:

      =IF(L4="Input mentioned in % IDV Monthly",HYPERLINK("Renseignement IDV 2013!A186"),"")

      and

      =IF(L4="Input mentioned in % IDV Monthly",HYPERLINK("[Renseignement_IDV_2013.xlsx]'Renseignement IDV 2013'!A186"),"")

      http://cjoint.com/?0JDwjkPyk0j

      Thank you again and have a good evening!!

      (PS: if you need more information, here is my email: ***@***)
      0
      1. via55
        Indeed, it's impossible to get the hyperlink to work despite several attempts:
        creating a new workbook with a copy of the data and saving before inserting the hyperlink; it works once but not thereafter...
        There seems to be a bug with hyperlinks depending on the versions of Excel.

        Hence, I propose an alternative solution with a macro (more efficient and safer).

        https://www.cjoint.com/?0JEa0XG90sU

        Macro placed in the Worksheet of the sheet.

        Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

        If Target.Column <> 12 Or Target.Row <> 5 Then Exit Sub ' if it's not the cell in row 5 column 12, nothing happens.

        If Cells(4, 12).Value <> "Saisie évo en % IDV Mensuel" Then Exit Sub 'if something other than Monthly Input in L4, nothing happens either.

        ' otherwise, position to cell row 186 of column 1
        ActiveSheet.Cells(186, 1).Select

        End Sub

        A double-click in L5 therefore redirects to A186 if the correct choice is made in L4.

        Best regards.
        0
      2. Gpa2Bol
        Hello via55,

        Indeed, your macro works for me as well.
        However, I don't master macros at all since I've never used this method before. So, I don't know how to access the "worksheet" of the sheet.

        If you could give me the steps, I would appreciate it.

        Thanks in advance :-)

        See you soon
        0
      3. via55
        Hello,

        Alt + F11 opens the macro editor
        In the left window, click on the name of your sheet in the hierarchy
        In the right window, switch from General (top left) to Worksheet
        and paste the macro

        Best regards
        0
      4. Gpa2Bol
        ```vba
        Sub MacroDirecte()
        If Range("L4").Value = "saisie évo en % IDV Mensuel" Then
        Range("A186").Select
        ElseIf Range("L4").Value = "Saisie évo en % IDV Excercice" Then
        Range("A196").Select
        ElseIf Range("L4").Value = "Saisie évo en % IDV 12 Derniers Mois" Then
        Range("A205").Select
        End If
        End Sub
        ```
        0
      5. via55
        Hello,

        The macro: (which you can adapt for other cells by copying a line and changing the coordinates within the parentheses)

        Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

        If Target.Column <> 12 Or Target.Row <> 5 Then Exit Sub ' if it is not the cell in row 5 column 12 nothing happens

        If Cells(4, 12).Value ="Input mentioned in % IDV Monthly" Then ActiveSheet.Cells(186, 1).Select : Exit Sub 'if Monthly Input in L4 position on cell line 186 of column 1

        If Cells(4, 12).Value ="Input mentioned in % IDV Financial Year" Then ActiveSheet.Cells(196, 1).Select : Exit Sub

        If Cells(4, 12).Value ="Input mentioned in % IDV Last 12 Months" Then ActiveSheet.Cells(205, 1).Select : Exit Sub

        End Sub

        Regards
        0
    3. pilas31 Contributor
      Hello to both of you,

      For your information:
      I just tested with the formula I suggested and it works perfectly on my end. Of course, the sheet needs to be renamed 'Renseignement IDV 2013' and the workbook should be called Renseignement_IDV_2013.xlsx

      I have Excel 2010
      Best regards,
      0
      1. Gpa2Bol
        Hello Pilas 31,

        Could you type out the formula that works for you?

        Thank you
        0
      2. pilas31 Contributor
        Hello,

        Here's the formula that works for me:
        =IF(L4="Input mentioned in % Monthly IDV";HYPERLINK("[Renseignement_IDV_2013.xlsx]'Renseignement IDV 2013'!A186";"JANUARY");"")

        I added "JANUARY" just to have a name in the hyperlink field but this argument is optional.
        0
      3. Gpa2Bol
        Hello Pilas,

        I just copied and pasted your formula but it's still glitching. It must be my Excel that has a little problem. As for the proposed macros, let's hope that Open Office can support this type of functionality since I'm creating the spreadsheet in Excel but it will need to be compatible with the .ods format.

        Best regards.
        0
    5. Gpa2Bol
      Hello to both of you,

      Thank you very much for your help, I opted for the macro solution.

      Thanks again!! :-)
      0
      1. Mike-31
        Hello,

        I had a possibility without a macro, which I tried to post unsuccessfully, here it is anyway if it is taken into account

        https://www.cjoint.com/?CJEibnOhlpt

        See you
        Mike-31

        A period of failure is a perfect time to sow the seeds of knowledge.
        0