Forum Discussion
How to get reference cell to update while closed
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.)
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 ;-)
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.)
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.
