SOLVED

How to keep a linked cell linked to a cell that changed location?

Copper Contributor

I linked cell C37 in Document PL to cell D60 in Document PR which has a value of 4.50. But I deleted a row in Document PR so now the previous cell D60 is now cell D59 and the new cell D60 has a value of 14.35. Cell C37 in Document PL updated its info to reflect the new cell D60. But I want cell C37 to automatically update a change in cell location to keep pointing to the cell that contains the value of 4.50. Is there a way to do that? 

6 Replies

@MsUser78 

You could use defined names for all referencing.  The catch is that the source workbook would need to be opened before the named reference can be read.

 

Power Query will read from Tables and named ranges without opening the source workbook to the user interface.  I presume the application must still open the workbook.

@Peter Bartholomew 

Can you tell me how to do it in an example? I'm not familiar with terminology and I could get something wrong.

best response confirmed by MsUser78 (Copper Contributor)
Solution

@MsUser78 

Probably not much use providing files because they will reference my user and folder details, not yours.

Put a few numbers in a row or column and give them a name by typing into the Name box (to the left of the function box) or using Define Name on the Ribbon Formulas tab.

 

Open a new workbook and type a formula that references the range in the first book e.g.

= SUM(Book1!Numbers)

where 'Numbers' happens to be the name defined in the first book and selected using the mouse cursor.

@Peter Bartholomew 

Thank you!

Btw, I don't know if MS devs saw my post because now the linked cell is automatically updated with the new location of the original cell if it changes. : D

(For other reader's reference here is a link about name boxes and how to use them: https://www.computerhope.com/issues/ch000704.htm , if link is broken just type "name box excel" and choose your pick. Be aware that name cannot contain spaces, at the time of this writing.)

@MsUser78 

Interesting, I will have to try it.

 

Mind you, it will make little difference to me because I do not use direct referencing.  I have on occasion described the practice of referencing data by its location relative to a worksheet as an abomination that should never have entered the world of professional model building (financial or engineering).  It is fit only for ad-hoc, disposable calcs.

 

You may not be surprised that few agreed with me!  That does not necessarily make me wrong though.

@Peter Bartholomew 

I think my earlier assertion that the cell location updates was not true after all. Today I was working in a document that had links in some cells and it got all messed up when I updated it because the cells the links pointed at had moved. 

1 best response

Accepted Solutions
best response confirmed by MsUser78 (Copper Contributor)
Solution

@MsUser78 

Probably not much use providing files because they will reference my user and folder details, not yours.

Put a few numbers in a row or column and give them a name by typing into the Name box (to the left of the function box) or using Define Name on the Ribbon Formulas tab.

 

Open a new workbook and type a formula that references the range in the first book e.g.

= SUM(Book1!Numbers)

where 'Numbers' happens to be the name defined in the first book and selected using the mouse cursor.

View solution in original post