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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,333
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,113
Messages
5,857,453
Members
431,880
Latest member
kar2rost

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top