How do I reference the cell value within a formula string

Question

To a cell in the worksheet, I'm attempting to establish a dynamic hyperlink. Dynamic means that instead of going to a specific worksheet in the workbook, the hyperlink will always go to the cell on the active sheet.

I was able to find the formula for a dynamic HYPERLINK for referencing the active sheet:

cell hyperlink =HYPERLINK("#D116",B37)

Now, I'm attempting to ensure that not just the connection remains dynamic, but also the reference cell. I want the cell number to update automatically whenever I add or remove rows. I reasoned that I couldn't do it using the original HYPERLINK function because the cell number is enclosed in quotation marks. I am therefore attempting to determine how to include the formula from Column C in the HYPERLINK function.

the value I need to incorporate in the HYPERLINK function =CHAR(34)&"#D"&ROW($A$116)&CHAR(34)

=HYPERLINK("&C37&",B37) does not work.

Is it possible? If not, is there another way of doing so? VBA, perhaps?

Answer 1

Try:

=HYPERLINK("#"&CELL("address",D116),B37)