Concat inside Vlookup Formula

AllMB

New Member
Joined
Mar 16, 2018
Messages
24
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
INDIRECT doesn't work on a closed workbook. You'd probably need some kind of macro to get that.
 

AllMB

New Member
Joined
Mar 16, 2018
Messages
24
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Working on a VBA option. Trying to get it to work now.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,030
Messages
5,526,341
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top