Excel External Reference Update One Drive & 365

Copper Contributor

I have created two excel workbooks that are supposed to talk to each other. They are both stored in the same one drive folder through 365.

 

The main problem is that both documents need to be open to update the information on the other workbook and return a result to the current workbook.

 

For Example, there is a workbook where I write down payments (Workbook 1) and another where I calculate a tax rate based on the payment from Workbook 1 (Workbook 2) and return a value to Workbook 1. Do I necessarily have to keep Workbook 2 open for the calculations to take place and get the result into Workbook 1? Or is there a way where calculations can still take place with Workbook 2 closed?

 

I hope this is clear enough. 

 

I want to know if there is a solution or a way around this issue or if there are no solutions yet. 

1 Reply

@SAAR95 

 

It's clear enough. I could be wrong about this, but if you are making changes in one and wanting those changes to be incorporated in the other and immediately available, then it stands to reason that they'd both need to be open. If you just want the change in workbook A to be available at a later time in workbook B, when you open workbook B--assuming the linkage back to workbook A is viable--that recent change in A will show when you open B. 

 

But....see my questions below.

 

[My point of reference: I download on a regular basis a data file (in Excel format) from my brokerage/bank). I link to that downloaded data via formulas in a "summary" workbook; the summary will show the most recently downloaded data when I open it. Unlike you, however, I don't make changes in the source file; the whole file represents change, in that it's newer than the older version.]

 

To your closing question, though, I have some questions for you:

  • is there a reason why you don't just combine the two into a single workbook? From your brief description, it doesn't sound as if anything would be sacrificed doing so (but I could be missing something).
  • might it make sense to move "the action" to the workbook where you write down the payments? That workbook could reference the tax rates (which, yes, might change from year-to-year but are basically stable for long periods) in the other.

I'm sure there are other questions I'd be asking if we were meeting face-to-face, and they'd all be dealing with your basic objectives here, and looking for a way to make the design achieve those objectives effectively.