Forum Discussion
How to keep a linked cell linked to a cell that changed location?
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.
Can you tell me how to do it in an example? I'm not familiar with terminology and I could get something wrong.
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.
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.)
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.
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.