Forum Discussion
Solved
How to keep a linked cell linked to a cell that changed location?
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?
- Do you have a location based policy in effect? If so, I would say yes that is your issue and not aware of another way around it.
6 Replies
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.
