Hyperlink to cell with shifting location

[himey77]

I am trying to Hyperlink to a continually moving cell within the same worksheet. However, the value of said cell (Text) does not change.

Also, the Workbook name is constantly changing. In addition, the columns must be able to be sorted.

Therefore, something like the following will not work because Test.xls will change and Defined Name only references the cell location which does not hold up to sorting:

=HYPERLINK("[Test.xls]Sheet1!Defined Name",Friendly Name)

FYI, I am a very basic excel user and I've learned most of what I know trying to figure out this solution! Please Help!

[davesexcel]

What does the workbook name and column sorting have to do with this?
Maybe supply a simple sample workbook for somebody to work with.

[JBeaucaire]

We could help create a hyperlink that would find a specific value across all the worksheets in your existing workbook and jump to it whereever it may be, and without a macro.

But if you add the need to do this "jump" from another workbook, I can't imagine how that would work without VBA.

'3D VLOOKUP & HYPERLINK
Here's a page showing a formula method doing a VLOOKUP across multiple worksheets. The formula will return the answer from the first sheet that has the searched value. There's a sample file as well.

3D VLOOKUP()

3D HYPERLINK()

[himey77]

Please see the attached example:

I want the user to be able to click on the red box that says "Click here for BCBS OF IOWA" (CELL G142), and the result is that the "BCBS OF IOWA" cell is highlighted (CELL C141).

However, neither the hyperlink cell (G142) nor the BCBS OF IOWA cell (G141) will remain static if the user chooses to sort the columns using Data -> Sort.

[JBeaucaire]

In post #3 I gave you a link to two sheets. The one with Hyperlinks have many formulas for hyperlinking.

You wanted to hyperlink on the same page, and the first formula given on that page does that exact thing. Did you see it?

"3D HYPERLINK"

1. # Hyperlink on same sheet:

=HYPERLINK("#" & ADDRESS(MATCH(A1, B1:B5, 0), 2), "Link")

Edited for your sheet:
=HYPERLINK("#" & ADDRESS(MATCH("BCBS of IOWA",C:C, 0), 3), "Click here for BCBS of IOWA")

Be sure to go through that formula, both versions, so you get what it does.

[himey77]

Edited for your sheet:
=HYPERLINK("#" & ADDRESS(MATCH("BCBS of IOWA",C:C, 0), 3), "Click here for BCBS of IOWA")

Thank you, Jerry!! The formula appears to do exactly what I needed!

I did try to work through your initial post, but I must admit it was a bit cumbersome for me because I am a novice excel user.

I will focus on breaking down the hyperlink formula before I tackle the more advanced spreadsheet example you posted.

Thanks again!!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[himey77]

Now that I have my hyperlinks working, how can I create a drop-down list with multiple hyperlinks?

Should I start a new topic?

[JBeaucaire]

Now that I have my hyperlinks working, how can I create a drop-down list with multiple hyperlinks?

Should I start a new topic?

Yes, this sounds like a completely different issue.

Start a new thread with supporting documents to make it easy for people to see/understand what you're trying to accomplish.