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