External Links to Other Excel Files Questions

[Origamipapier1987]

I have an issue with an excel file. Within the excel file there is a sheet that’s taking the results of a database query, and then using external links through five different excel files with pricing structure, business rules, product facts, and foreign exchange rates. Maybe I’ve forgotten some Connection lInk knowledge as of late. When the department that runs that model file processes it and saves it, I then proceed to open it to review and what I have noticed as of late is that if I click do not update links toward external files the final figures on the file do not match up to amount that the team A provided to me from the same file on the same date an hour earlier. However, if I open it and then update the links they do articulate to the same value. This was not occurring before.

The reason I am questioning it is because the files it looks up to are live files which are being updated by another Team B. I need to be able to know what was originally in the file that team A was running, before Team B overrode that cell with another figure.

Somehow, that’s not occurring as of late. I would have thought that at the moment I save the file, I’m not just saving how the financial figures were, but also the arrays/external links to other documents as they were at that precise time. So that if I open it again, and press “Do not update links,” it would remain as it was at the time of the last save.

I need to know the rules for external links. And why the file is saving the figures, but apparently not saving the external link totals as they were at that time.

 

[Jerry Sullivan]

Welcome to MrExcel,

I think you have the correct understanding of how the updates to the external links work. I'd suggest you do some controlled testing to ensure that the sequence of events is occurring exactly as you think it is.

You noted

"...the final figures on the file do not match up to amount that the team A provided to me from the same file on the same date an hour earlier."

How did the team A provide those figures earlier- was it a print, pdf, screenshot or an Excel file?

One scenario could be that team A is updating the external link files, opening the "report" file, updating links, making a pdf, then not saving the workbook (or saving it to a different file path than you expect).

 

[Origamipapier1987]

The Datasheet within the file that they run (and I support) has a pivot table which aggragates the cost toward product location and type of overall fee. They take this and copy into another excel file and that's the one that I review. With a saved version of the file they worked on. Since the one that understands their file the most due to being the SME for costs, is myself. As such I review and get back to both teams and provide feedback. Or correct the legacy system aka that particular file that Team A executes.

Since finding defects is what I do primarily, I decided to take the file and save it in my own drive. I opened it and saved a version as Binary. I then ran the test and compared the findings when I forced both to update and then forced both to not update. The binary one did not update when I instructed it not to, and the file gave off the same amounts as the team's results. The file they currently used does not. I'm wondering if it is that we saved this as Macro instead of Binary, and considering the file has augmented over the last three years with new company products, and new locations the file expanded in size and new business rules where we really did need a new system.

Still testing though.