SOLVED

How to get reference cell to update while closed

Copper Contributor
Hello, I have not been able to find an answer to my question on several forums, hopefully you all can help me out.

A simple example of what I am looking to do is:

In Book1 cell A1 = book3 cell B1
In Book2 cell A1 = book3 cell B1
In Book3 cell B1 = 10, all other cells are empty.

Book1 is open
Book2 is closed

I open Book3 copy Column B and insert the copied cells before Column B. Now B1 and C1 both equal 10. I change the value of C1 from 10 to 20.

Book1 cell A1 updates to 20 because the formula follows the cell to its new position of C1.

When you open Book2 cell A1 still equals 10 because the formula still references cell B1 in Book3.

Is there a way to make the formula is Book2 update when the column was added in Book3 even when Book2 is closed?

Thank you
5 Replies
best response confirmed by -AbsoluteZero- (Copper Contributor)
Solution

@-AbsoluteZero- 

Hi, there!

I suggest you try the following:

On Book 3 select the cell and give it a name, for example, CellToMonitor (see picture attached.)

1.JPG

Save.

Open Book1 delete the formula on cell A1, and then write it again by pointing to cell B1 in Book 3. You should get something like this on your formula bar: 

=Book3.xlsx!CellToMonitor

Save

 

Open Book2 and do the same as in Book1.

Save

 

Close all books.

 

Test ;)

 

 

This is brilliant!

It will take me a while to run though the few thousand cells to name them, but the amount of time this will save and the increased accuracy will be well worth it.

Thank you so much Celia!
You're very welcome! I am glad that I could help. Let me know if you need help to name that many cells. Maybe there's a way to do that quickly.
One thing I do notice that causes a delay in accuracy is if I change the value in my source workbook and don’t save because I don’t want to keep the changed value, any open workbooks referencing that cell will hold the last value until a new workbook is opened that also references that named cell, I open the source workbook, or I close and reopen the destination workbook.

Is there a way to update the link when closing the source workbook?

FYI, If I implement this I will have multiple destination workbooks each with hundreds of links in it. And these destination workbooks are being opened and closed by my staff throughout the day, along with the source workbooks.

It would be great to see these update in real time, but the delay between opening and closing gives me a little pause.

@-AbsoluteZero- 

Is there a way to update the link when closing the source workbook?

Maby this way:

On the open referencing workbook: tab Data, button Edit Links, Update Values.

To be automatic, maybe with a macro in the source workbook that, before closing would tell all the other books to update the links. 

Maybe there is something that you can change in your process that would avoid this struggle. 

 

1 best response

Accepted Solutions
best response confirmed by -AbsoluteZero- (Copper Contributor)
Solution

@-AbsoluteZero- 

Hi, there!

I suggest you try the following:

On Book 3 select the cell and give it a name, for example, CellToMonitor (see picture attached.)

1.JPG

Save.

Open Book1 delete the formula on cell A1, and then write it again by pointing to cell B1 in Book 3. You should get something like this on your formula bar: 

=Book3.xlsx!CellToMonitor

Save

 

Open Book2 and do the same as in Book1.

Save

 

Close all books.

 

Test ;)

 

 

View solution in original post