Hyperlink to cell locations that change

[PatrickD2209]

I would like to hyperlink (sheet 1, B5) to another worksheet (sheet 2, C3) in same workbook.  Sheet 2 is always being sorted so the hyperlink from sheet 1, B5 always returns the data from sheet 2, C3 but due to the sorting it is not the correct information.  How can I create a hyperlink  from sheet 1, B5 that is always linked to the correct data from Sheet 2 regardless of any sorting?

[#2]

@PatrickD2209 

It sounds mostly like a re-design is needed. I tried naming the range (Insert....Name...Define) before sorting, but it still referred to C3...

Can you share a bit more info:

• what is it about Sheet 2, C3 per se that makes it the sole and necessary reference for some formula or function in "Sheet 1"?
• Why is the column (table?) in Sheet 2 being sorted? Is that a necessary thing? [If you or another user are needing to be able to look at a sorted version of the data in Sheet 2, as the column or table gets larger, and if that different view is necessary, it's possible to use the new Dynamic Array functions to produce that sorted array in a different spot (perhaps a different sheet) in the same workbook, without actually altering the original]

Is it possible for you to upload the workbook (devoid of any confidential data)?