Hyperlink to cell but to a cell with a changing row number

[JackDanIce]

Hi,

At the top of my worksheet, I have a summary table of four regions. These take subtotals from the rows below, which are grouped by each region

I would like to create hyperlinks in my summary table to then go directly to the row below it relates to, however, because I insert and delete rows, I'm not sure how to make my hyperlinks dynamic.

E.g. Four regions are A, B, C and D
A is currently found in A10, B in A25, C in A40 and D in A60

If I insert 3 rows above row 15, A is still in A10, B is now in A28, C in A43 and D in A60 (to confirm, summary table and linked cells are all on the same worksheet)

How can I make the hyperlinks in the summary table change automatically so that when clicked it takes me straight to the new row location instead of the previous one?

I found this on the search but doesn't answer what I'd like:
http://www.mrexcel.com/forum/showthread.php?t=504088&highlight=dynamic+hyperlinks

I want to avoid using a macro/VBA solution if possible

Thank you in advance,
Jack

 

[RoryA]

Should be doable with a HYPERLINK formula using MATCH to get the row number.

 

[JackDanIce]

"It's easy when you know the answer"

Should have thought of that, didn't know you could put formula into the hyperlink box thing. I'll have a go and report back if stuck.

Thanks Rory,
J

 

[RoryA]

HYPERLINK is a worksheet function - I'm not referring to the Insert-Hyperlink option.

 

[JackDanIce]

Cheers for clarifying, will look at that function