0

Here is my situation:

I have a base spreadsheet called "data" which does not reference any other spreadsheets.

I have 10 intermediate spreadsheets, each with lots of external links to "data" which work as I would expect: When I change something in "data" and save, the intermediate spreadsheets update, either in real time or when next opened.

Finally, I have a "summary" spreadsheet, collecting data from the intermediate spreadsheets, also by external reference. Note, "summary" does not directly reference "data" directly, only indirectly through the numerous intermediates.

"Summary" does not act as I would expect. I would expect to be able to open only "data", change something, then open "summary" and see the changes reflected. But what I see is data in "summary" updating only after I manually open the intermediate spreadsheets.

Is this normal behavior? Is there a way to get to the behavior I expect?

Note, all documents are "trusted" documents, and on sharepoint.

Note, when I open "summary" I see alerts at the bottom of the screen that it is opening the intermediate spreadsheets one by one, automatically. This does not actually open them on screen, it appears to be a euphemism for reading the data. But the data from "data" does not flow through.

Anonymous
  • 136
  • 2
  • 13
  • The 'intermediates' only update to reflect changes to 'data' once they are opened (and go through a recalc cycle which pulls in the new values from 'data'.) If they are not opened they are left in the state they were in when they were last closed. Consequently, if you change 'data' and don't subsequently open the 'intermediates' the 'summary' will not reflect the changes to 'data'. There are ways around this but they (most likely) require use of VBA. – DMM Jul 30 '23 at 10:18
  • @DMM As Socrates said, I wish I'd known that before. – Anonymous Jul 30 '23 at 17:29

0 Answers0