Vlookup not working when source files are closed

[Tjeu]

Hey guys,

I have a file with a lot of linked data, which can't find any data via the Vlookup function when the source files are closed. It works perfectly until I decide I want to sort the data in a different way, which is when I get #REF! errors all over the place. However, when I open the source files it all works again.

Does anyone know what the cause of this might be and how I can fix it? I double checked the code to the source like a 100 times and I don't think that's what's wrong.

 

[6StringJazzer]

Many formulas do not work on closed files, but I believe VLOOKUP should work on a closed file. Can you show an example of your formula? Describe what you are sorting.

 

[Tjeu]

=VLOOKUP(A3;'D:\Coachen\(2022-2025) Dalto A1\2022-2023\Statistieken\1e Helft Veld\Algemene Statistieken Dalto A1 2022-2023 1e helft Veld.xlsx'!Table5[#All];2;FALSE)

This is the code on one of the cells.

The sorting or something doesn't matter. It also happens with every other thing I do with the data, like removing or adding rows in the same table, etc.
Also, when I check which files are linked to the main file, it says it cannot make any connection with them.

 

[Fluff]

You cannot use structured references if you want the formula to work with a closed workbook. You will need to use ranges instead.

 

[Tjeu]

Aaaah yes, that solves it!!

Thanks so much!

 

[Sufiyan97]

You cannot use structured references if you want the formula to work with a closed workbook. You will need to use ranges instead.

Hi Fluff
What is structured reference?

 

[Fluff]

Glad we could help & thanks for the feedback.

@Sufiyan97 have a look here Structured references in Excel tables

 

[6StringJazzer]

Hi Fluff
What is structured reference?

A structured reference is when you write a formula using Table names and Table column names in the formula. In the sample formula above:

=VLOOKUP(A3;'D:\Coachen\(2022-2025) Dalto A1\2022-2023\Statistieken\1e Helft Veld\Algemene Statistieken Dalto A1 2022-2023 1e helft Veld.xlsx'!Table5[#All];2;FALSE)