vlookup doesn't always work in this situation

[Tim L]

I'm using Access vb to copy an Excel spreadsheet to a new filename, then fill in values from a recordset. The source .xls has formulas that use vlookup() and match() to find values in a third spreadsheet. (The values to be looked up and matched are blank until my code fills them in.)

When I open the newly created spreadsheet, I get the msg asking me if I want to refresh the lookups. If I say NO, then all my formulas containing lookup() and match() work as expected. If I say YES, then all of them return #NA. If I say NO then try to edit one of the formulas, that formula turns to text.

Any suggestions? Thanks.