Dynamic Hyperlink based on value in cell range

[mmonaghan]

I am looking to create a dynamic hyperlink to navigate to cell based on value rather than a specific cell address. Here is my scenario:

Workbook “Client Renewal Forecast” sheet “2015 Forecast” cell A67 = client name

Sheet “'Rollforward by Product” is pivot table where somewhere in range A:A the client name will appear.

I want to create a hyperlink on sheet “2015 Forecast” cell A67 where the client name hyperlinks to the pivot table “Rollforward by Product” where the client name appears in A:A.

I tried the variations of the following.

=HYPERLINK("#'Rollforward by Product'!" & ADDRESS(MATCH('2015 Forecast'!A67, 'Rollforward by Product'!$A:$A, 0), 1), "Client Name")

When I hit enter after typing in the formula above, explorer opens and I navigate to the file where a pop up box of the tabs appears. I pick the tab for the pivot table which modifies the formula seen below. If I don’t do this than the hyperlink reads as N/A. If I do, the hyperlink reads as the client name but either way neither of the formulas jumps to range A:A client name on the Rollforward by Product tab.

=HYPERLINK("#'Rollforward by Product'!" & ADDRESS(MATCH('2015 Forecast'!A67, '[Rollforward by Product]Rollforward by Product '!$A:$A, 0), 1), "Client Name")

Your assistance is appreciated in helping me correct my formula.

Michelle

 

[NARAYANK991]

Hi Michelle ,

Your formula is working for me ; can you see the sample file ?

Narayan

 

[mmonaghan]

Narayan,
Thanks for responding. I can't share my file as it is customer sensitive. My plan was to do a mock of my file and share that with you but the formula works in my mockup file. I have tried various tests to determine why it works in my mock file but not in my production file. The production file pivot table was connected to an outside datasource but that doesn't seem to be the problem. The production file had a password to modify which I removed but no luck. There is a large volume of calcuations in the file but I can't see that that would influence the hyperlink formula. I am not sure what I can do to get your assistance if I can't replicate my issue in a non-customer sensitive file format.

 

[NARAYANK991]

Hi Michelle ,

Can you break your formula down to its constituents , and see what each of them returns , once you put these constituents in your production file ?

1. =MATCH('2015 Forecast'!A67, 'Rollforward by Product '!$A:$A, 0)

2. =ADDRESS(MATCH('2015 Forecast'!A67, 'Rollforward by Product '!$A:$A, 0), 1)

Suppose the first constituent returns 36 ; this means the second one should return the string $A$36.

Now try the third constituent of your formula as follows :

3. =HYPERLINK("#'Rollforward by Product'!" & "$A$36", "Client Name")

At what stage do you get an unexpected result ?

Narayan

 

[mmonaghan]

1. In mock up file =MATCH('2015 Forecast'!A67, 'Rollforward by Product '!$A:$A, 0) yields 2
in production file =MATCH('2015 Forecast'!A67, 'Rollforward by Product '!$A:$A, 0) yields see screen shot and cell reads as #N/A
2. mockup up file =ADDRESS(MATCH('2015 Forecast'!A67, 'Rollforward by Product '!$A:$A, 0), 1) yields $A$2
production file yields same screen shot.
3. mockup file yields the hyperlink
production file when entering the formula cell reads as Client Name rather than N/A. When clicking cell for hyperlink reference is invalid

Michelle

 

[mmonaghan]

Additionally, I didn't get a cell reference result for checking the constituent result of the address/match formula so I just plugge a cell reference in the test for step 3.

 

[NARAYANK991]

Hi Michelle ,

It is clear that the MATCH function does not return a match ; from what I can see , the tab name is not correct.

Can you double click on the tab names in your production file , and see whether they match the following :

1. '2015 Forecast'

2. 'Rollforward by Product '

Narayan

 

[mmonaghan]

Narayan,
I did try what you said and it didn't work. I checked this with another excel power user in my company who hadn't used hyperlinks in this way but understood your directions and she ended up getting the same result with my production file and mockup as I did. I want to thank you for your assistance.