conditional hyperlink to different sheet

[ahartman]

Hi all,

This one seems straight forward, but I can't get it to work. I have a list of numbers in a pivot table and a count of the occurrence of those numbers as the data in the table. If there are repeats of the number, I want to do a MATCH for the number in the pivot table's data list, and provide a link to the row on that list sheet. The number is in column B, the count of it's occurrence is in column J. The column to match against on Sheet1 is K.

Here's what I've tried:

=IF(J42>1,HYPERLINK("""Sheet1!A"&MATCH(B42,'Sheet1'!K:K,0)&"""","# " & B42),"")

This creates text when I want to in the correct cells, and the text is blue and underlined as if it's a correct hyperlink. But when I click on it, it says I can't open the file, even though I'm currently in the file. If I select a different cell and just use the right-click Add Hyperlink functionality, I can happily link to the exact same desired cell. This doesn't work, though, if I want them to be conditional, and I don't want to have to add hundreds of links manually.

Does anyone have any advice?

Thanks,
Adam