Concat inside Vlookup Formula

[AllMB]

I am trying to pull data from another excel spreadsheet while it is closed using drop down menus to change the year and month. The reason for the drop down is to change the file that is referenced as I will be saving the files with the correct year and month.

Here is what I have:

=VLOOKUP(A126,INDIRECT(CONCAT("'[",A121," Position Control"," ",F2," ",E2,".xlsx]Position Control'!$A$2:$B$2")),2,FALSE)

A121 is the division name, F2 is the month, and E2 is the year.

The file name is "Division Name" Position Control January 2019.

I need to be able to pull the information the the file closed. The formula works fine when the file is open but it needs to work when the file is closed.

Trying to make the process of pulling in the required data idiot proof as people still mess it up on a regular basis.

 

[Eric W]

INDIRECT doesn't work on a closed workbook. You'd probably need some kind of macro to get that.

 

[AllMB]

Working on a VBA option. Trying to get it to work now.

 

[Eric W]

Good luck! Let us know how it works.

 

[AllMB]

Working on it here: https://www.mrexcel.com/forum/excel-questions/1088352-vlookup-vba-errors.html