hyperlink not change after deleting or insert the cell

When I use the hyperlink function to point to a cell on another sheet in the same workbook, the hyperlink doesn’t adjust dynamically with the cell when it is moved. i.e. I use the formula =HYPERLINK("#'Calcs. Form'!D12","1-100"), but if I delete cell D11 on Calcs. Form and my D12 cell moves up, my hyperlink still points to D12 instead of moving up to D11.

The only solution I have found is to actually give the cell a unique name via define name and point a hyperlink at the name. Suggestions? Am I just missing something in my formula? I don’t have the D12 set as absolute. ($D$12)

Super User Asked by SUMIT JAIN on November 14, 2021

2 Answers

2 Answers

One way to insert a possibly changing cell reference as TEXT in a formula:

The "short" form:
="The cell referenced is"&cell(D12;"address")&" and it contains '"&D12&"'"

The same:
=CONCATENATE("The cell referenced is";cell("address"D12);" and it contains '";D12;"'")

(Untested formulas - ) Note the use of ; as parameter separator, this will be , e.g. in English locale and probably others too.

Answered by Hannu on November 14, 2021

The problem is that the "D12" is in a string, not a standalone cell reference. You can quickly upgrade that with the following approach:

=HYPERLINK("#'Calcs. Form'!D"&ROW('Calcs. Form'!D12),"1-100")

The ROW() function will still pass "12" to the string being built along with "#'Calcs. Form'!D" but now it is an actual cell reference so if you delete D3, say, and the cell desired becomes cell D11, the reference in the formula will change to D11 also and ROW() will return "11" not "12" so the hyperlink will be told the correct cell.

You can use this technique of combining text and actual cell references in any function that needs a string. So instead of giving it a simple, hardcoded string, you build a string with the portions that must be adaptive (adjusting for cell/row/column deletions like here), or using input from other cells to create a dynamic address (so you can create "dynamic ranges" for instance, or both in this case and with INDIRECT() you might wish to dynamically build a reference). Yet another use is perhaps you have many tabs of identically arranged data, just for different months/years perhaps, and wish to have a reporting sheet on which a user might type a month or year or whatever, and data from the right tab is thereby dynamically selected without having to nest an IF() for each tab in a monster formula. Very simple technique, yet useful in a million places.

Answered by Jeorje on November 14, 2021

Add your own answers!

Related Questions

Ask a Question

Get help from others!

© 2021 All rights reserved.