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 AnswersOne 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

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!

Recent Questions

- How to segment a group of symmetric points
- Prove $D(A):= {win H^{2}(0, 2pi): w(0) = w(2pi), w^{‘}(0) = w^{‘}(2pi) }$ is dense in $L^{2}(0,2pi).$
- A is greater than B by 25% then by what percentage B is less than A?
- Show continuity of partial derivatives of piece-wise function
- For any two positive operator $a,b$ with norm $<1$ there is a $c$ such that $a,bleq c$ and $|c|<1$. Does this hold for $|a|=|b|=1$?

Recent Answers

- NeitherNor on How to segment a group of symmetric points
- Ethan Bolker on A is greater than B by 25% then by what percentage B is less than A?
- Dominique on A is greater than B by 25% then by what percentage B is less than A?
- zkutch on A is greater than B by 25% then by what percentage B is less than A?
- Harish Chandra Rajpoot on A is greater than B by 25% then by what percentage B is less than A?

© 2021 InsideDarkWeb.com. All rights reserved.