When I filter a column, my hyperlinks go to the "wrong" location

A little about my spreadsheet:

This is a document used to show which of our customers have which of our products. The main page is a list of all customers with columns for each product. There is an "x" in a product column if a customer has that product. For instance: (not perfect, but you get the idea!)

Customer	Product 1	Product 2	Product 3
John Doe	x		x
Jane Doe		x

I have hyperlinked the "x" in each column to navigate to another tab in the workbook - a specific product page. Here, it lists just those customers with that product and more applicable information regarding the specific product. I have made it so that these pages can be filtered by either the customer-specific number we assign them OR customer name. The default is customer number.

To hyperlink, I assigned a defined name to each customer number. It works great if you're filtering by customer number. But, if you filter by customer name, the hyperlink doesn't adjust to the filter, but instead jumps to the customer number that it was originally attached to. I understand why this would be the case, but I'm wondering if I can change this and have the hyperlink jump to a specific customer number, no matter where it is on the page.

Please keep in mind that since customers can have multiple products, the customer number may be listed on multiple tabs.

Hopefully my post makes sense! Feel free to ask for clarification.

Thanks,
Aliese

A sample spreadsheet may be needed for this. It is difficult to duplicate the problem you are having.
Why not set the hyperlinks to the product labels on the main page?

Originally Posted by mgdac

A sample spreadsheet may be needed for this. It is difficult to duplicate the problem you are having.
Why not set the hyperlinks to the product labels on the main page?

With the volume of customers that we have, it was requested that the hyperlinks be set to the customer's specific entry on the applicable product page for easy access. I can't disclose any customer information, meaning I cannot share my exact spreadsheet, but I'll try to come up with a sample spreadsheet that would duplicate the problem.

Sample_Hyperlinked_Spreadsheet_5_22_2013.xlsx

Okay, I've created a sample spreadsheet. It's drastically simple compared to the real one, but I think you'll get the idea. Try filtering to move the order of customers around (on the product pages) then click on the hyperlink. It won't match up like I had hoped. Thanks.

Originally Posted by XOR LX

There's probably a much neater way to do this, but this will work:

Instead of inserting hyperlinks into each of your cells, enter HYPERLINK formulae: for example, in cell D2 of the Customers tab enter:

=HYPERLINK(AdamSandlerClue,"x")

Change your defined name for AdamSandlerClue to:

=INDIRECT(ADDRESS(MATCH("Adam Sandler",OFFSET(Clue!$B$2,0,0,COUNTA(Clue!$B:$B),1),0)+1,1,1,1,"Clue"))

Edit: It's actually simpler to do without Named Ranges, as this allows you to use dynamic references in your table, rather than 'static' text. You'll also be able to copy this formula over to the other cells without having to change anything, i.e. in cell D2:

=HYPERLINK(INDIRECT(ADDRESS(MATCH($B2,OFFSET(Clue!$B$2,0,0,COUNTA(Clue!$B:$B),1),0)+1,1,1,1,D$1)),"x")

Regards

Thank you for your response! I tried everything you suggested and didn't get anywhere with it :/ I got a "Circular Reference Warning" when trying to copy the last formula. Not sure if I did something wrong or what.

Originally Posted by XOR LX

Try the below.

Have made a modification so that it doesn't need amending for each sheet to which it refers. Paste this formula into cell C2 of the Customers tab and copy across to all other cells:

=HYPERLINK("#"&ADDRESS(MATCH($B2,OFFSET(INDIRECT(C$1&"!$B$2"),0,0,COUNTA(INDIRECT(C$1&"!$B:$B")),1),0)+1,1,1,1,C$1),"x")

You should get a few "#N/A"s, which obviously correspond to cases where that person is not found in that particular sheet. If you wanted, you could amend the formula to notify the user of this, e.g.:

=IFERROR(HYPERLINK("#"&ADDRESS(MATCH($B2,OFFSET(INDIRECT(C$1&"!$B$2"),0,0,COUNTA(INDIRECT(C$1&"!$B:$B")),1),0)+1,1,1,1,C$1),"x"),"No Entry")

Regards

Thank you. I tried your adjustment and while it worked in that it took me to the correct page, I ran into the same issue as I initially posted. When I filter by Customer Name on any of the product pages, it changes the order of the list (as it should), but then if you click again on the hyperlink, it takes you to the same place every time, regardless of what is now in that cell.

Example:

I put this formula in all cells that previously had an "x" in them on "Customers" tab: =HYPERLINK("#"&ADDRESS(MATCH($B2,OFFSET(INDIRECT(C$1&"!$B$2"),0,0,COUNTA(INDIRECT(C$1&"!$B:$B")),1),0)+1,1,1,1,C$1),"x")

When I click on an x, it goes to the appropriate cell, as it should. However, when I filter by "Customer Name", it reverts back to the original location. So, take the "Clue" tab. Filtering by "Customer Name" should put "Adam Sandler" at the top of the list. When I click on D4 on the "Customers" tab, Adam Sandler's "Clue" entry, it takes me to A3 on the "Clue" tab, which is now Tim Meadows line...

My goal at the end of the all of this is to have the hyperlinks go to the appropriate spot regardless of its location on the spreadsheet. That way, if I (or someone I share the spreadsheet with) wants to filter it, it will never go to the wrong location. Can your formula be edited to make that happen? Or is there no way to do that?

Thank you!

Originally Posted by XOR LX

Did you remove your original hyperlinks from the cells in the Customers tab? If not, highlight them all, right-click and Remove Hyperlinks.

Regards

It worked like a charm!! Thank you a million times over

Originally Posted by XOR LX

Did you remove your original hyperlinks from the cells in the Customers tab? If not, highlight them all, right-click and Remove Hyperlinks.

Regards

Okay, it worked for the first column, "C," but when copying over to the other columns, I get a #REF! error. Do I need to edit the formula for the other columns?

Originally Posted by XOR LX

Are you getting these errors when you try it in the version which you attached to this thread? All is fine for me. Remember, if you've made some amendments to the sheet so that it differs from that which you originally posted, it's kind of hard for me to help you.

A #REF! error could be several things, but can you check firstly that your column headers in C1, D1 and E1 of the Customers tab (i.e. Monopoly, Clue and Chess) match precisely the names of the tabs to which they are referring?

Regards

They match. I just retyped them and the first one appeared to work. The second one was a two name Column - "Parts Store." I changed it to Parts_Store and it appeared to work. But now the problem I run into is the last 5 entries in this column come up with an N/A, as if there is no entry. There clearly is. Could part of the problem be that the Customer Names are the same? Basically, it's the same customer, just different divisions. The different is reflected in the Customer Number (A store vs B store vs C store). It seems like the formula feeds off of the Customer Name. Can that be edited to feed off of the Customer Number instead? That one will always be 100% unique. Thanks.

I have inserted a hyperlink event macro.

No error trapping was done, so you have to make sure you have legit values.

No range names are necessary. (I have deleted all of them)

Make all hyperlinks in each column the same. (yes exactly the same).

Just like before each link points to the appropriate page.