1

I have an Excel file that was created a long time ago, and one of the columns uses a VLOOKUP formula that references a file that does not exist on any computer. Formula:

=VLOOKUP(C25,'F:[my_file.xls]abc'!$A$1:$F$65536,2,FALSE)

The F: drive does not even exist on my computer. What's confusing is this sequence of occurences:

  1. Insert a value in cell C25.
  2. Dialog box comes up asking me to link to F:my_file.xls.
  3. I hit cancel because file doesn't exist.
  4. Formula resolves to a correct solution anyway.

So it seems like there's a hidden formula or something that runs like a backup to the VLOOKUP formula? I have Unhid all sheets, columns, and rows, and I found nothing that explains this. Anyone know what's going on? Thanks

1 Answer 1

2

When dealing with external data sources, Excel usually saves the current values of cells referring to linked data in case the data source isn't available when the workbook is reopened later.

You can change how this situation is presented to the user with Data -> Edit Links -> Startup Prompt...

enter image description here

As you can see from the second option (above), it's entirely possible to open the workbook without updating the linked data and not even alert the user.

Also, when working with data connections (similar to linked workbooks but usually database queries) you can choose whether or not to embed the external data in the workbook as shown below.

enter image description here

3
  • I see. Is there a way to display the saved data from the missing workbook?
    – rabbid
    May 14, 2013 at 3:49
  • @Rabbid, I don't think so. You may be able to infer what some of the data was but it would likely be incomplete. Excel saves just enough - not a complete copy. May 14, 2013 at 4:02
  • I don't kniw how important the data is for you, but if the Excel sheet is stored in the newer xml format (.xlsx) you could rename de file to .zip, unzip it and examine the contents. Maybe some data could be recovered that way. Haven't tried such a thing though...
    – agtoever
    Jan 29, 2015 at 18:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .