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
Calcs. Form and my
D12 cell moves up, my hyperlink still points to D12 instead of moving up to
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. (
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&"'"
=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")
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
1 Asked on August 1, 2020 by finn-rup-nielsen
1 Asked on July 29, 2020 by igrimpe
2 Asked on July 27, 2020 by saadq
1 Asked on July 26, 2020 by cicekadam
4 Asked on July 26, 2020 by aarav-prasad
2 Asked on July 24, 2020 by marcusw
Get help from others!