External referenced cell/Only updates when both workbooks are open

Purple Turtle

New Member
Joined
Aug 2, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
I'm having issues with an external referenced cell. The formula for the link looks like =[Workbookname.xlsm]Sheet1!$A$1
The workbooks are both on the same drive. The destination cell will only update when both workbooks are open at the same time.
Is it possible to allow the update to happen when the destination workbook is closed?
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
When both workbooks are open, close the source workbook before you close the destination workbook. If you do this you should notice that the formula in the destination workbook has changed to include the full path to the source workbook. In other words the formula now looks something like:
='C:\Users\Joe\Desktop\[Workbookname.xlsm]Sheet1!$A$1
where the bit in red will be specific to your computer.
If you closed the source workbook when calculation was set to manual, the destination workbook formula will update to include the full path, but the cell will not change. To change it while the source workbook is still closed, press F9 to effect calculation and the linked cell should update.
 
Upvote 0
I have the updated link and calculation us set to automatic. Still only updating when both are open.
 
Upvote 0
I have the updated link and calculation us set to automatic. Still only updating when both are open.
Have you closed the source workbook (first), while leaving the destination workbook open and looked at the formula in the destination cell to see if it changes from:
=[Workbookname.xlsm]Sheet1!$A$1
to this:
='C:\Users\Joe\Desktop\[Workbookname.xlsm]Sheet1'!$A$1
??
 
Upvote 0
Yes. From what I've read, excel doesn't allow updating when another book is closed unless maybe using a query. I can get it to update once but a second time requires reopening the source workbook.
 
Upvote 0
Yes. From what I've read, excel doesn't allow updating when another book is closed unless maybe using a query. I can get it to update once but a second time requires reopening the source workbook.
I'm not sure I understand your issue then. If you update the destination workbook once when the source workbook is closed, you can't make any further change to the source workbook unless you open it. So, if you haven't opened the source workbook again why would you need to update the destination workbook a second time?
 
Upvote 0
I'm not sure I understand your issue then. If you update the destination workbook once when the source workbook is closed, you can't make any further change to the source workbook unless you open it. So, if you haven't opened the source workbook again why would you need to update the destination workbook a second time?
There is a lookup formula in the source workbook for that particular cell and so it is always being updated. The destination workbook is not open at that time.
 
Upvote 0
There is a lookup formula in the source workbook for that particular cell and so it is always being updated. The destination workbook is not open at that time.
Could have saved us both some time if you had stated that in your OP. Always best to provide the details rather than simplified examples that don't accurately reflect the actual circumstances.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top