Can I retrieve lost data through vlookup?

[big_les]

Hello everyone.

The problem: I have a workbook (db1) with a huge amount of data in it. It includes a vlookup that was linked to a second huge workbook (db2). I lost db2 and need it back!

So, when I open db1 I see that the formula in the cell still points to db2 and returns the result from there. In fact it points to the file system on my new laptop, even though db2 is not saved there (it is lost). When I recalculate the formula with a minor change (e.g. ask it to return the result from db2 column 2 instead of db2 column 1, or change the lookup row) it works and doesn't give me an "#N/A"! Soooo, I figure, excel must be reading from db2 in the background and maybe my data is not lost at all but saved somewhere hidden? Can anyone tell me if this is right? and if so, can I get my data from db2 back??

Eternal gratitude to anyone who can help with this. It would make my year.

[MrShorty]

Go to the Data group and click on Edit links to bring up the edit links dialog box. From this dialog, you can identify the linked workbook file, its path and location, open it (if it still exists), etc.

[big_les]

Thanks MrShorty, but this doesn't seem to work. If I check the status it gives "error: Source not found" because the file is not in that location (lost).

But even then, I can change the formula and it returns the value. So, I am sure excel has the data stored from the lost database, but somewhere in the background. Getting to it is the problem.

[big_les]

I slept on the problem last night and here is a solution in case anyone looks at this in the future. It was glaringly simple.

If you have lost a second data source (db2) which is referred to in an existing data source (db1), you can retrieve db2 by following these steps.

1) db1 had the vlookup in cell B2 referring to column 2 of db2: =VLOOKUP(B2,'C:\Users\CRT\[CRT CHW Enrolment Survey NGR KDZ.xlsx]Enrolment Survey'!$A$1:$BK$320,2,FALSE)
2) Edit the cell (f2) and copy the text of the formula. DON'T copy the cell itself - this will move the B2 reference to C2 and mess up the retrieval. Copy the text to keep B2.
3) Go to the next cell (C2) and paste it. It will return the same answer as in B2.
4) Edit the formula to refer to column 3 instead of 2: =VLOOKUP(B2,'C:\Users\CRT\[CRT CHW Enrolment Survey NGR KDZ.xlsx]Enrolment Survey'!$A$1:$BK$320,3,FALSE)
5) Repeat this along the row, each time editing the formula to show you bd2 column 4, 5, 6, 7, 8, etc. This step takes the time, but it's worth it.
6) The data from the lost db2 will be returned into db1 (which you should now save as db2)
7) Once you have edited all the columns in row 2, you can simply copy and paste down to the end of the data (row 320 in my case).
8) Copy the entire wroksheet, open a new worksheet, paste-special "values" and this will remove the formulas.

I crosschecked this for a known record (i.e against the raw data sheets) and the data from db2 was correct.

Data recovered!

I have saved myself days of work. I am happy and hope you will be too.