VLOOKUP formula results unexpectedly change from correct values to #REF!

[au2010]

I have two files, call them FileA.xlsx and FileB.xlsx.

FileA contains VLOOKUP formulas that refer to a range in FileB.

When both files are open, the VLOOKUP formulas work perfectly, returning exactly the expected results.

But when FileB is closed, the VLOOKUP formulas in FileA return a #REF! error.

If FileB is then opened, the VLOOKUP formulas immediately work perfectly again.

Does anyone know why this would occur? Any idea on how to trouble-shoot it or what settings that might be affecting this?

I am using Excel 2016.

Thank you.

 

[AhoyNC]

Does your VLOOKUP state the full path to file B?

Something like
VLOOKUP(lookup value,'C:\data\[FileB.xlsx]Sheet1'!Range,column number,FALSE) of course changing the part in bold to the actual file path.

 

[Fluff]

What is your vlookup formula?
Does it refer to a structured table?

 

[Lian]

It is not concern of vlookup. There are some fuctions called Volatile functions which would cause errors. It works only the refering file is open.

 

[Fluff]

@Lian welcome to Mrexcel

It is not concern of vlookup.

That is not true, there are situations when a standard vlookup will behave as the OP as stated.

 

[Lian]

@Lian welcome to Mrexcel

That is not true, there are situations when a standard vlookup will behave as the OP as stated.

Hi Fluff, thanks for your reminding and point out this for me.
I think it it much better to check his formula or screen capture.

 

[Fluff]

I think it it much better to check his formula

Which is why I asked the Op to post the formula.

 

[au2010]

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.

 

[Fluff]

You're formula looks wrong, I think it should be

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

Other than that I can see no reason why that formula would exhibit the problems you are having.

 

[jasonb75]

@Fluff, I don't think that the typo that you've identified would result in an error only when the source workbook is closed.

@au2010 Do any precedent cells (or named range definitions) contain an INDIRECT reference? Precedent would mean cells that are referred to in the formula, if those cells contain formulas then the cells that those formulas refer to, and so on.