Share via

Hyperlink within Excel using the Formula

Anonymous
2015-04-02T13:56:20+00:00

I'm trying to create a hyperlink in Sheet 1 of an excel workbook that links to Sheet 2 within the same workbook using the HYPERLINK() command so that I can place the hyperlink within an IF() function. I know how to create a hyperlink within the same excel document WITHOUT using the formula, but this does not allow me to utilize the IF() command. Can this be done and, if so, how?

I've already fooled around with using the command HYPERLINK(CELL(filename,[friendly name])), but I do not know how to add "'Sheet2'!A1" to the end of the addressed which is returned by CELL(filename). This may be the completely wrong way to go about creating the hyperlink, but I couldn't think of any other solutions.

*EDIT*

I apologize. I should have been more clear before; I am not looking for a value from Sheet2. I am trying to make the IF() command display the hyperlink should certain conditions be met. For instance:

     IF(A2=5,HYPERLINK('SHEET2!'A1,"Link"),"")

However, this syntax will not work as the HYPERLINK() command must have the file path entered into it. So, to clarify I am not looking for a value from SHEET2. I am simply trying to have a hyperlink that appears if certain conditions are met and that takes me to SHEET2. I understand the IF() function well; it's just the HYPERLINK() function which gives me problems

*END EDIT*

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2015-04-02T17:12:36+00:00

    You could try the formula with the following syntax:

    =HYPERLINK("#Sheet2!A1","link")

    Thanks, vvijay!!

     I did try the syntax; however, the '#' is not accepted by Excel for this formula. Also if I keep the quotation marks around #Sheet2!A1, then the formula simply displays the information and does not create a hyperlink.

    Hi,

    Try this:

    =HYPERLINK("#'Sheet 4'!B7","Sheet 4!B7")

    Note the single quotes around the sheet name and the double quotes around the whole string.

    1 person found this answer helpful.
    0 comments

14 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-04-02T16:59:36+00:00

    Thank you, Richard! Unfortunately, I am not attempting to pull any data from SHEET2. I am just looking for a way to create a hyperlink using the HYPERLINK() command which would take me from SHEET1 to SHEET2. The IF() command would just be to show the hyperlink should certain criteria be met.

    0 comments
  2. Anonymous
    2015-04-02T16:30:36+00:00

    Hi Volgona,

    There is a difference in putting a hyperlink in a cell that refers to another place in the workbook and getting the value of a cell somewhere else in the workbook.
    Sheet2!A1

    This is an hyperlink that will bring me to Sheet2, cell A1.

    If I want to test the value in Sheet2!A1 e.g. =IF(Sheet2!A1 ....

    Type in the cell =IF(

    and just select the Sheet and the Cell, Excel will add it automatically to the formula.

    0 comments
  3. Anonymous
    2015-04-02T14:56:11+00:00

    Hi Volgona,

    You could try the formula with the following syntax:

    =HYPERLINK("#Sheet2!A1","link")

    Hope this helps!

    0 comments
  4. Anonymous
    2015-04-02T14:53:36+00:00

    Hi Volgona,

    If you have Sheet 1 and Sheet 2 and you want to Link them.

    Copy the cell you want to link to e.g A1 on Sheet 1

    Right Click  on the Cell where you want that link e.g. A1 on Sheet 2

    Then choose the Paste Special "LINK" (Chain) option.

    On Sheet 2 in the Cell you should have a formula that looks like this

    ='Sheet 1'!$A$1

    Then you can use this within the IF()

    =IF('Sheet 1'!$A$1<20,'Sheet 1'!$A$1*2,'Sheet 1'!$A$1)

    When using the IF, the first part in the brackets has to be a Logical Test to evaluate as TRUE or FALSE

    Therefore 'Sheet 1'!$A$1 must be compared to another Cell reference or Value e.g <20 or =A2

    Hope that helps

    Richard Bailey

    If you found that helpful, here is a useful resource with some free Excel training manuals, that might help you in the future.

    http://www.microsofttraining.net/excel-training-london.php#resources

    0 comments