MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Hyperlink in IF statement


Posted by Maarten on January 20, 2002 7:58 AM

IF($F$34="Gebouw verbeekstraat";HYPERLINK(Hoofdscherm!;"GO");"NO DATA")

With this formula, I want to go to another worksheet in Excel called Hoofdscherm. I want Excel to give the cell the name GO. So IF someone select in the list the name "Gebouw verbeekstraat" and he pushes the button GO he automatically go to the worksheet "Hoofdscherm"

What do I do wrong?

Is there a direct way from the list to other worksheets, I tried a list of hyperlinks but it will not work.

Help Thanks

Maarten (HOLLAND)


Posted by DK on January 20, 2002 12:28 PM

Hello Maarten,

I think you have to include the workbook name and a specify a range in the worksheet. E.g. say your workbook is called MyBook this will work:-

=IF($F$34="Gebouw verbeekstraat",HYPERLINK("[MyBook.xls]Hoofdscherm!A1","GO"),"NO DATA")

HTH,
D

Posted by Maarten on January 21, 2002 2:25 AM


Oke I tried that too, but it gives #NAME?
IF($F$34="Gebouw verbeekstraat";HYPERLINK("[ruimteboekversie6.xls]Hoofdscherm!A1");"GO");"NO DATA")

Posted by Aladin Akyurek on January 21, 2002 3:33 AM

Selection, Hyperlink, & GO button

Maarten --

Make first a 2-column list of the items of interest (like Gebouw verbeekstraat) and associated URL's (NOT the hyperlinks) in a woksheet named Admin. E.g.,

"Gebouw verbeekstraat,[ruimteboekversie6.xls]Hoofdscherm!A1;
Gebouw2,[ruimteboekversie6.xls]Hoofdscherm!A70;
Gebouw3,[ruimteboekversie6.xls]UitgeleendeBoeken!B18"

Select the cells of the first column and name it e.g., GEBOUWEN via the Name Box on the Formula Bar.
Select all of the cells of the 2-column list mentioned above (excluding any labels/column headings) and name it e.g., HPATHS via the Name Box on the Formula Bar.

Activate F34.
Activate Data|Validation.
Choose List for Allow.
Enter as Source:

=GEBOUWEN

Click OK.

Activate F34 and name it Selection via the Name Box on the Formula Bar.

Put a button in F35 whose name is edited such that it reads GO via the Forms toolbar.

In a cell, say, A2, in a worksheet that you could name Blackboard enter:

=HYPERLINK(VLOOKUP(Selection,HPATHS,2,0),Selection)

Start recording a macro with the name GO.

Navigate to Blackboard and just click what you see in A2.

Stop recording the macro.

Give a right-click on the GO button. Activate the option Assign Macro and assign the GO macro to this button.

Now, you can select some item from the drop-down selection list and just click the GO button which will navigate you to the desired location in the workbook.

Hope this covers what you have in mind.

=============

Posted by Maarten on January 23, 2002 12:00 PM

Re: Selection, Hyperlink, & GO button

Works great thank you alladin