Hyperlink to a cell on another sheet in Excel

1
votes

I am working on Excel report in which I need to pass a link which can take me to a cell referenced.

In below example, I have two sheets on same workbook. Now if I click on cell "A1" on Sheet:1, then it should take me to cell "E1" on Sheet:2.

I know by paste special I can pass hyperlink, but tricky part is data on Sheet:2 is dynamic so I can't go that route.
After looking on Google, I have found out multiple things, so I clubbed them together but it is throwing error "Reference is not valid".

Here is the formula that I've used on cell "A1" on Sheet:1: 

=HYPERLINK("[Sample.xlsx]Sheet:2!(ADDRESS(MATCH(""ABC"",BEFORE,0),MATCH(""ABC"",BEFORE,0),1))","ABC")

Here BEFORE is a name range that covers A1 to E1.

Sheet:1

    a      b      c      d      e

1.  abc

Sheet:2

    a      b      c      d      e

1.  x      x      x      x     abc

Appreciate your inputs.

1
vbaexcelhyperlinkexcel-formula
try to remove double quotes: ""ABC"" --> "ABC". And do you really have sheet Sheet:2 or correct would be Sheet2? – Dmitry Pavliv
@simoco, if I remove that double quotes, then formula throws an error. – ManishChristian
do you really have sheet Sheet:2 or correct would be Sheet2? – Dmitry Pavliv
btw, you can't do it like this: "Sheet2!(ADDRESS..." – Dmitry Pavliv
@simoco, thanks for the response, I do have name on sheet2, I've just used Sheet:2 for reference. And this is what I got so far, I don't know other way for my requirement. – ManishChristian

1 Answers

5
votes
=HYPERLINK("[Sample.xlsx]'Sheet2'!" & ADDRESS(1,MATCH("abc",BEFORE,0)),"ABC")

If your sheet name might have spaces then make sure to add the single quotes around the name.