This is the OP. Thank you for the replies. Sorry I was slow to come back on. To address the comment/questions raised above:

1. The vlookup contains the full path and is in the format noted above ("**VLOOKUP(lookup value,****'C:\data\[FileB.xlsx]Sheet1'!Range,**column number,FALSE)" except that "column number" is replaced with a match formula.

So the VLOOKUP in FileA looks like this, where the data is in the range a1:z1000 in FileB:

VLOOKUP(x1,**'**C:\data\[FileB.xlsx]Sheet1'!A1:Z1000,MATCH(x2,**'**C:\data\[FileB.xlsx]Sheet1'!A1:a1000,0),FALSE)

where X1 is the Lookup value and X2 is the Match value (the exact path is of course different, but the full and correct path is included in both the VLOOKUP part and the MATCH part).

2. The data in FileB is not a structured table. It is just a regular range of cells, as noted above. Column A contains a list of employee names entered as text. Row 1 contains product names also as text. Other cell values are numbers representing sales totals. So, for example, if "Joe Smith" is the value in Cell A4 and, and "Light "Bulbs" is the the value in Cell D1, then Cell D4 = sales of light bulbs by Joe Smith. The purpose of the VLOOKUP formula is to return the sales figure for any combination of employee name and product.

3. As long as both FileA and FileB are open, the VLOOKUP works perfectly.

4. But if FileB is closed, and the formulas in FileA are recalculated, the VLOOKUP returns #REF

5. If FileB is reopened, and FileA is recalculated again, the #REF goes away and the correct value is returned. This is without any changes being made to the formula.

6. Furthermore, if **both files** are closed, and only FileA is reopened, the VLOOKUP appears to return the correct value. But as soon as the file is recalculated, the #REF error returns until File B is also reopened.

This makes no sense to me, but perhaps someone with better Excel knowledge can explain it.

Thank you.