IF function and Hyperlink

Hello pilas

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

Best regards

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.

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

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.

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

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

```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 ```

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

Hello Pilas 31,

Could you type out the formula that works for you?

Thank you

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.

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.