Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
    
I see. Is there a way to display the saved data from the missing workbook? –  rabbid May 14 '13 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. –  Mike Fitzpatrick May 14 '13 at 4:02
    
thank you very much! –  rabbid May 14 '13 at 7:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.