Excel formula returning values from missing workbook

0
votes

I'm having an unusual issue with Excel 2013.

There is an old Lookup formula in the workbook which is referring to another workbook. However, this workbook no longer exists and has been deleted from the server. The problem is the lookup formula still returns values when the reference cell (lookup value) is updated!

Has anyone seen this before or have any ideas why this would happen?

Here's the formula for reference

    =LOOKUP(Control!AQ15,'C:\Users\xxxxxxx\Desktop\[Copy(1)NAS 16Jun15.xlsm]Articles'!B1:B82,'C:\Users\xxxxxxx\Desktop\[Copy(1)NAS 16Jun15.xlsm]Articles'!C1:C82)

No idea why they're still using Lookup, but this shouldn't happpen...

1
excelexcel-formula
it looks like the file is on C drive – Rosetta
That's where the formula is pointing to, but the file is not there. Besides, the formula is updating for all users, and they only have access to their own desktops. This being the case, the formula should not update as they won't have access even if the file was there. – PeteBradshaw
Seen this happen before, for a variety of reasons. Most common is that the program has "remembered" the values, and then inputs them. Seemed to happen on networked PCs before 2006 or so. Other option is that the file is hidden, and was on a PC that was mirrored as the base PC, and the file is in fact still there. – Clauric
aside the file being hidden from plain sight, did you accidentally shared your desktop folder to everyone? – Rosetta
Hi Clauric, the PC's aren't mirrored from a standalone machine, they get fresh builds sent remotely from our IT suppliers, so the information must be stored somewhere in the workbook as you suggest. Do you know if Excel has some sort of cache where this would be stored? If so, I'll like to try and clear it as it's quite clear that the data we're using can't be trusted. – PeteBradshaw

1 Answers

1
votes

Found the answer!

I've just looked in the Advanced Options section under the "When calculating this workbook:" heading and found an option to "Save external link values".

This was ticked so I've deselected it, saved it and then reopened and the problem has been solved!