XLOOKUP and Closed Workbooks

[Matt N]

I have started using XLOOKUP a lot as it for the myriad of reasons given in videos as well as it is easier to explain to my boss how I am handling errors.
However, sometimes I get #REF! errors when the formulas are looking into closed workbooks.
This is not a consistent error, so I cannot say exactly when it happens.

Is this a known "Feature?"

 

[Fluff]

Hi & welcome to MrExcel.
If you open the other workbook, does the #REF! go away?

 

[Matt N]

The #REF! is still there.
I can F2 + Enter to get results back.
Thank you

 

[Fluff]

Is the formula looking at a table in the closed book?
If so are you using structured references?

 

[Matt N]

Is the formula looking at a table in the closed book?
If so are you using structured references?

Not using structured references.

 

[Fluff]

In that case I'm not sure why. Using table references rather than ranges will cause that problem, but that's the only thing I can think of.

 

[Matt N]

In that case I'm not sure why. Using table references rather than ranges will cause that problem, but that's the only thing I can think of.

Thanks for replying quickly.

 

[Fluff]

Sorry I couldn't have been more help.
Hopefully somebody will post other ideas.

 

[DKoontz]

In that case I'm not sure why. Using table references rather than ranges will cause that problem, but that's the only thing I can think of.

Finding I'm having this issue too, unfortunate that table references causes the link to break until the workbook is reopened.