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.
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.
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.)
