I have searched for a solution and I believe what I want to do is possible (or maybe it is not), but I can’t seem to make it work.
An outline of the problem is as follows. I have some data in column A of sheet Data, the values in column A are not unique. In sheet Usage I have another set of data in column A which is a unique list of the data in sheet Data. I want to create a hyperlink on the cells in the Data sheet and the hyperlink to take me to the corresponding cell in the Usage sheet based upon the value of the cell clicked in the Data sheet. In principle this is simple to set up a number of hyperlinks however when the data in the Usage sheet is re-ordered the hyperlinks do not re-order with them so the hyperlinks point to the wrong cells.
After scouring the internet it appeared that setting up dynamic named ranges and hyperlinking to said named ranges would solve the problem as the named ranges would order with the values in the Usage sheet. I defined a new named range, let’s just call it “matchRange” and applied the following reference to it:
=INDEX('Usage'!$A$3:$A$533,MATCH("1115",'Usage'!$A$3:$A$533,FALSE),1)
I know this works because if I apply this formula in a cell it returns 1115. Now if I go to the cell in the Data sheet and insert a hyperlink on the cell containing 1115 I do not see the “matchRange” name in the Defined Names section of Insert Hyperlink. So I guess I am asking should I be able to see this name to attach the hyperlink too? If I just create a name range to a cell I can then attach a hyperlink to the name I defined for it.
Just to point out I do not want to use the hyperlink formula in cells for this.
Hope that makes sense, any assistance would be appreciated. Thanks
