Retaining formula result when exporting WS to Text files

Hi

I have a workbook that contains many worksheets that have been generated from a column of data contained in work sheets in the same workbook, here is the code:

Please Login or Register  to view this content.

Most of the data in the cells is the same on everysheet but there are some that change by referencing data on sheet1, with this formula (and with a great deal of help from Brokenbiscuit on this forum)
="DIST="&VLOOKUP(VALUE(MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,3)),Sheet1!N:P,3,FALSE.

This works great while the data is in the worksheet, However when I export the sheets as a text file the resulting text file has #VALUE! instead of the result of the formula. Here is the code I am using for creating text files from each of the worksheets:

Please Login or Register  to view this content.

Any Ideas on how I can keep the formula result in the exported text files?

Tim

Hi Leith

Thanks for the reply, unfortuntaley this didn't work. It seems to only export sheet1 and none of the others. To cut to the chase I have attched a copy of the work book in question:
In a nut shell the sheets have been generated from cells in sheet one that represent files names built up from various info in the sheet. A lot of the data is the same but the Dist (decimal Milage) and FLN (filename) change with each section. Each of the sheets represent header data for a file that will contain XY data for the profile of a rail tunnel (generated from laserscan data), the next stage will be to append the XY data to the header file.
In answer to your question all the data is within this workbook and does not reference any other.

Cheers

Tim