Excel 2007 : Crash when saving after vlookup

I'm trying to solve an issue for a user where they are performing a vlookup and the wookbook crashes when saving AFTER the vlookup calculation is performed. The spreadsheet is very large (70K+ rows) and the wookbook it is linking to for the lookup is bigger again (900K+ rows). It doesn't matter whether I calculate for 1 row or the whole spreadsheet, it still hangs when trying to save (there is no error message). The formula i'm using is

=VLOOKUP(B2,'[code listing.xlsx]Sheet1'!$B$1:$C$900000,2,FALSE)

Sheet looks like this:


| A | B |C | D | E | F | G|H| I |
1| - |projserial | -| - | - | - | - | -|Allocation |
2| - | 220002 |- | - | - | - | - | -|derived price/gain
3| - | 220002 |- | - | - | - | - |- |derived price/gain
4| - | 260021 |- | - | - | - | - |- |derived price cost+
5| - | 100063 |- | - | - | - | - |- |lump sum
6| - | 100063 |- | - | - | - | - |- |lump sum

(The dashed fields have data in them but are not relevant to the lookup)

The vlookup formula is in column I

The "code listing" sheet that it is looking up is like this:

| A | B | C |
1| - | 100000 | lump sum |
2| - | 100001 | lump sum |
3| - | 100002 | lump sum |
4| - | 100003 | lump sum |
5| - | 100004 | lump sum |
6| - | 100005 | lump sum |

This continues on to 900K with different values in the C column.

(Sorry about the drawings html takes all the spaces out!)

I can't work out whether its an add-in problem (crashes on multiple machines) an invalid data entry (It would take me a long time to sort through 65K+ entries!). Is the formula correct? Can I use some sort of alternative that won't crash?

Please help!

Geoff

anybody? would it help if I posted the files in a zip archive?

Hi Geoff,
it looks like a good idea, but don't post the entire fiel, just a sample will do

unfortunately this crash isnt reproduced when I only take a sample of the file. It seems to occur only with the full file. Would anybody be willing to take alook if I upload it to rapidshare?

Maybe it's long winded but you could try it out with 100 k rows, than 200, etc.. and see when it crashes
If you can post a sample maybe some alternatives ( VBA f.i. ) can be found?