How to force a cell value to change if Linked cell moves?

[Dennis]

XL 2003


Below is a summary of sheet information


SHEET NAME CELL AMOUNT LINK
Summary by deal B32 4,000,000.00 B32

The (Sub Address) Link "B32" on Sheet "Summary by Deal" is calculated
by SubAddress = SHEET NAME + CELL (was B32).

If the cell positions on Sheet "Summary by Deal" change then this Link
will fail.

I realize that I need to re-calculate the Link by re-doing
SubAddress = SHEET NAME + CELL (now B33).

More important and the challenge:

How do I get the CELL value to change from B32 to B33 when the row is
inserted in Sheet "Summary by Deal?"

Is there a smarter/better way to solve?

TIA EagleOne

 

[Dave Peterson]

You can name all the ranges that you hyperlink to and then you won't have this
trouble.

But I'd dump the Insert|Hyperlink style of hyperlinks and use the =hyperlink()
worksheet function.

Since it's a worksheet function, it'll react to the inserting/deleting of
rows/columns.

David McRitchie posted this and it might help you:

=HYPERLINK("#"&CELL("address",C5),C5)
=HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
=HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)

 

[Dave Peterson]

Ps. I'd use an adjacent cell with a formula like:

=HYPERLINK("#"&CELL("address",'summary by deal'!b32),"Click Me")

to try to make it more obvious to the user that it's a hyperlink.

 

[Dennis]

Did try ADDRESS() but to no avail. I thought that the Returned value
for ADDRESS() would change as I added a Row on Sheet "Summary by Deal"
but it did not.

BTW, I can do this easily in VBA but the users want it to happen in
Excel.

 

[Dennis]

Dave,

You and I posted about the same time. I am now testing your
information.

Thanks EagleOne

 

[Dennis]

Dave,

=HYPERLINK("#"&CELL("address",'summary by deal'!b32),"Click Me")

I am trying to make the hard-code 'summary by deal'!b32 (above)
relative by concatenating the information in cells B2 & C2.

I have tried =HYPERLINK("#"&CELL("address",C2&B2),"Click Me") and
=HYPERLINK("#"&CELL("address","'"& C2 "'!" & B2),"Click Me") and
neither works.

Thoughts?

Thanks EagleOne

 

[Dennis]

Dave,

This should work because it displays in the Fx wizard "'"&B2&"'!"&C2
but...

 

[Dennis]

Another try,

This does link but it is not volitile:
=HYPERLINK("#"&"'"&B2&"'!"&C2,"Click Me")

EagleOne

 

[Dave Peterson]

How about:

=HYPERLINK("#"&CELL("address",INDIRECT("'"&C2&"'!" & B2)),"ClickMe")

Where C2 held the name of the worksheet
and B2 holds the address to go to.

 

[Dennis]

Thanks Dave

The formula does pull the data. Unfortunately, it is not volitile.
BTW, I never would have thought of Indirect(). For me slowly but at
least forward thanks for the MVPs.

EagleOne

 

[Dave Peterson]

This creates a hyperlink that you can click on and go to that other cell.

If you want to retrieve a value from that cell, you don't need the =hyperlink()
function.

=indirect("'"&C2&"'!" & B2)
or maybe
=if(=indirect("'"&C2&"'!" & B2)="","",indirect("'"&C2&"'!" & B2))

I guess I'm not sure what you're doing.

Thanks Dave

The formula does pull the data. Unfortunately, it is not volitile.
BTW, I never would have thought of Indirect(). For me slowly but at
least forward thanks for the MVPs.

EagleOne

 

[Dennis]

Dave,

Sheet1 has information that is used in Sheet2. Sheet1 Column B
displays the sheetname, and Column C, the cell reference to those cells
on Sheet2.

My goal was to have the OP click on a link in sheet1 which would take
them to the respective receiving cells on Sheet2.

In addition, Sheet1 opens in the left 1/2 of the screen and Sheet2 on
the right 1/2 of the screen.

To do this, I need information that is volitile (on Sheet1) so that
Sheet1 Columns' B & C cells know the current position of the receiving
cells on Sheet1.

Visually and logically, it works great. But all fails if the columns
and rows change on Sheet2.

I really was not looking for hyperlinks per se. Just a way to find and
to "BorderAround" the "To" and "From" cells.

Gosh, I hope that I made the challenge clear.

Dave Peterson wrote:

 

[Dave Peterson]

I still don't understand what you mean by if the columns and rows change on
sheet2. How would they change and how do those changes get updated on Sheet1 in
columns B and C?

(And BoarderAround means Select that cell????)

Dave,

Sheet1 has information that is used in Sheet2. Sheet1 Column B
displays the sheetname, and Column C, the cell reference to those cells
on Sheet2.

My goal was to have the OP click on a link in sheet1 which would take
them to the respective receiving cells on Sheet2.

In addition, Sheet1 opens in the left 1/2 of the screen and Sheet2 on
the right 1/2 of the screen.

To do this, I need information that is volitile (on Sheet1) so that
Sheet1 Columns' B & C cells know the current position of the receiving
cells on Sheet1.

Visually and logically, it works great. But all fails if the columns
and rows change on Sheet2.

I really was not looking for hyperlinks per se. Just a way to find and
to "BorderAround" the "To" and "From" cells.

Gosh, I hope that I made the challenge clear.

 

[Dennis]

Dave,

Sheet2 is the "Master" w/s. Sheet1 is a place where "constants"
(numbers) which were buried in formula strings on Sheet2 are displayed
so that each can be labeled and easily reviewed. Also, it provides
"history" as to how the constants change for each monthly financial
closing. (A new column is added for each monthly closing)

To aid the reviewer and the OP in understanding the linkages, I
provided two windows one for each w/s.

The constants on Sheet2 were sent to Sheet1 and a link from 1 to 2 was
made. That is the Right to Left connection. To display and "tag"
(BorderAround) the respective cells in the respective Windows, means
that Sheet1 cells must know where the Sheet2 cells are (as new columns
and rows are added on sheet2 over time.

Therefore, Sheet1 columns Column B (various Sheet names) and Column C
(Cell refs) are fine the first time. Through time though, Sheet2 cell
address's will change and sheet1 needs to be smart enough to maintain
current Column B * Column C references.

Hence the need for volitile information in Sheet1 i.e. certain forms of
Hyperlinks.

EagleOne

 

[Dave Peterson]

I've never seen this kind of formula
=HYPERLINK("#"&CELL("address",'summary by deal'!b32),"Click Me")
not get updated when b32 "moves" on the other sheet.

If you always want to point at a certain address, try the =indirect() version

=if(=indirect("'"&C2&"'!" & B2)="","",indirect("'"&C2&"'!B2"))

 

[Dave Peterson]

And if you drop the references to the other cells, you can point directly at the
cell to hyperlink to with:

=HYPERLINK("#"&CELL("address",'summary by deal'!b32),"Click Me")

And if you insert/delete rows or columns or change the name of the sheet, the
link will continue to point at original cell--no matter where it was moved to.

 

[Dennis]

Excellent Dave!

Your point about the sheetname, I had not even considered - Duh!

Here is the code that I am using.

**********************************************
Set the original formula:

With Sheets("Constants
Input").Range("F:F").SpecialCells(xlCellTypeVisible)
myCell.Formula = "=HYPERLINK(""#""&CELL(""address"",'" + _
Trim(myCell.Offset(0, -4).Value) + "'!" + _
Trim(myCell.Offset(0, -3).Value) + "),""Click Me"")"
End With

**********************************************
Make current the Sheetname:

With Sheets("Constants
Input").Range("F:F").SpecialCells(xlCellTypeVisible)
BeginStr = Len(Left(myCell.Formula, InStr(myCell.Formula, "'") +
1))
SecondStr = InStr(BeginStr, myCell.Formula, "'")
SheetRef = Mid(myCell.Formula, BeginStr, SecondStr - BeginStr)
myCell.Offset(0, -4).Value = SheetRef
End With

**********************************************
Make current the Cell Reference:

With Sheets("Constants
Input").Range("F:F").SpecialCells(xlCellTypeVisible)
BeginStr = Len(Left(myCell.Formula, InStr(myCell.Formula, "!") +
1))
SecondStr = InStr(BeginStr, myCell.Formula, ")")
CellRef = Mid(myCell.Formula, BeginStr, SecondStr - BeginStr)
myCell.Offset(0, -3).Value = CellRef
End With
**********************************************

Dave, thank you for your outstanding help

EagleOne (Dennis)