CODEWITHSUNDEEP
excelhyperlink
Adjit
Adjit

Reputation: 10305

Excel add hyperlink to another table in excel

I am trying to add a hyperlink from one sheet to a named table on another sheet. I know that if I name the range then I can use the hyperlink, but ideally, I would like to keep it as just a table.

I have a table on SHEET 2 named MySpa - I would like to have a link from SHEET 1 to that table.

I tried this - =HYPERLINK(MySpa, "Link to MySpa Table") but when I click on the link in the cell it doesn't seem to do anything. How can I link from one sheet to a table on another sheet?

Upvotes: 1

Views: 9960

Answers (4)

Wilbur Omae
Wilbur Omae

Reputation: 314

If the table is named and is in the same sheet, prepend a hash (#) and wrap in double quotes as shown below:

=HYPERLINK("#TableName", "Click to view")

If the table is in another sheet, prepend the sheet name as shown:

=HYPERLINK("#SheetName!TableName", "Click to view")

The trick is in wrapping the reference in double quotation marks and including the hash sign.

Upvotes: 4

Gourav Saha
Gourav Saha

Reputation: 1

Excel does not recognize a full table in hyperlink, howver there is a simple workaround provided you want to show a single cell from a table, you can use it's cell address to point as a hyperlink.

Suppose your table is named MyTable

=HYPERLINK(CELL("address",INDIRECT("MyTable"),"Click")

It will show the first row and column intersection in the table, and it will not be static if you cut paste the table to anywhere it will not get affected since it is based on table object name.

If you are having a call from a different table with same number or rows and want to call the corresponding cell of another table then just add [@ColumnName](the column name you have) inside INDIRECT() along with table name like

=HYPERLINK(CELL("address",INDIRECT("MyTable[@ColumnName]"),"Click")

Now it will reference to the same row number as is called from the requesting sheet.

Upvotes: 0

Andreas Covidiot
Andreas Covidiot

Reputation: 4755

(Tables don't show up (at least in Excel 2010), but it's still possible...)

  1. You can create a named reference pointing to the (named) table, e.g. name it LnkMyTab and let it point to =MyTab. Now LnkMyTab should appear in the hyperlink dialog under Current Document and it will be automatically updated as the table may change :)

  1. Another kind of workaround (at least in Excel 2010) based on static nature of hyperlink formulas is this:

    (glossary: T: my table, A: some (temporary) anchor, L: my hyperlink)

    1. rename your tab MyTab to MyTab_ (T)
    2. create some temporary named reference MyTab to some arbitrary range or cell, e.g. =B10 (A)
    3. create your hyperlink using MyTab (L)
    4. delete the temporary MyTab (A)
      • (the hyperlink's formula will not be updated and would bring an error if clicked now. this is the "feature" we are using here :))
    5. rename your tab MyTab_ back to MyTab (T)
    6. voilá - the hyperlink should work now

Upvotes: 0

atclaus
atclaus

Reputation: 1176

Right click on the cell you wish to have the link, hyperlink, place in this document. If you have named the table you should see it there. If not, make it a named range.

Upvotes: 0

Related Questions