Software

Ask something
Question Locked

Hyperlink in Excel with IF function

0 helpful
6 replies

by chris.goh78 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I would like to know how to create a hyperlink within an Excel file that would move from either sheet1 or sheet2.

For example, the hyperlink will take me to Sheet1 if the content in Cell A3 = A and the same hyperlink will take me to Sheet2 if the content in Cell A3 = B.

I have tried using the hyperlink function but the drawback would be the limitation by Excel filename. Should I rename the Excel file, the hyperlink will not work. Any idea on how to resolve this? Using Macro perhaps?

Thank you.

6 replies

by absolutely

Which cell is to contain the hyperlink? I understand that the URL in the hyperlink depends on the content in Cell A3, but where is this hyperlink itself? Please add any other missing details, too. This question is too vague to provide a helpful answer so far.

by chris.goh78

Let's assume this scenario. I have an Excel file with the name Test.xls and within there are 3 worksheets, namely Main, Production and Supply.

In the Main worksheet, I want to calculate the cell A3 to determine if it is Production or Supply. Also, in cell C3, I have this hyperlink that will take me to the respective worksheet, depending on the value in cell A3. Thus, in cell C3, I have this formula
=IF(A3="P",HYPERLINK("[Test.xls]Production!D2","NEXT >>>"),HYPERLINK("[Test.xls]Supply!D2","NEXT >>>"))

What this does is that if the value in cell A3 = P and I click on the hyperlink of cell C3, it will take me to cell D2 of Production workheet.

This has been working well so far, until there's a need to change the filename. The alternative is to change the hyperlink function to reflect the current file name in order for the hyperlink to work.

My query is, are there any other method that I can use to perform the same function mentioned above. This is because the file name tends to change and yet I still want the hyperlink to work.

Looking forward to see what you / anyone have in mind and thank you.

by absolutely

use the file name "Personal Macro Workbook.xls", the default location for storing Excel VBA macros that will be available to all Excel files. If you do all your work each month in a file with that name, stored in the directory C:\...\XLSTART11, and only change the file name after executing the macro as many times as necessary, you'll have a simple workaround while Ernest digs through his books!

by deadly ernest

by having the formula give the IF statement and then used a Visual basic reference for the sheet and cell concerned. If referring to another file it was different to referring to another sheet.

I can't find my old examples but it went something along the lines of

IF X (SHEET2,A32)

Substitute the sheet or file name for the word Sheet. BTW They didn't have the term HYPERLINK as one of the acceptable formula words when I did this. The exact wording should be in the normal Excel Help files under Formula - Reference - Sheet

I'll dig around for some of my old examples and books.

by deadly ernest

you can reference a cell or set of cells on another sheet by using =SheetTitle!CellReference example =Sheet2!B6

you can even 'Name' the destination cell or cells and use that. Eg name the destination cell as 'Sales' then the formula becomes =Sales instead of Sheet2!B6 - the usual rules about grouping cells in a reference also apply.

If you want to reference to a sheet in another file then you have to use the Visual Basic reference method to include the File name as well as the sheet and cell method as above.

This discussion is closed to new replies.