Vlookup from external workbook

mattless1

Board Regular
Joined
Apr 1, 2011
Messages
89
Hi All,

Can someone help me with this formula below.
i wish to extract a cell value from a different workbook. The workbook name stays the same, but the date changes everyday

workbook Name is IBT PRINT LIST 22nd July 2019.xlsx

=VLOOKUP(B8,'[IBT PRINT LIST 22nd July 2019.xlsx]Sheet1'!B$2:X$47,5,0)

Any help would be greatly appreciated.
 

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
501
Office Version
365, 2013
Platform
Windows
Hi,

You can use the following formula to "produce" the adjusted Workbook Name for a given day (TODAY()):
Code:
="IBT PRINT LIST "&DAY(TODAY())&IFERROR(CHOOSE(MOD(DAY(TODAY()),10),"st","nd","rd"),"th")&" "&TEXT(TODAY(),"mmmm yyyy")
You can then reference this value (let's say it's in cell D9) in your VLOOKUP formula:
Code:
=VLOOKUP(B8,INDIRECT("'["&D9&".xlsm]Sheet1'!B$2:X$47"),5,0)
Of course you can combine these two into one formula but I thought it would be clearer to show how each component is working separately :)

Hope it helps.
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,751
Office Version
365
Platform
Windows
Keep in mind that the workbook will need to be open. INDIRECT will return #REF ! error if the workbook is closed. * Unless it has changed in Excel 2016 (untested).
 

Forum statistics

Threads
1,082,447
Messages
5,365,574
Members
400,838
Latest member
Renedescartes

Some videos you may like

This Week's Hot Topics

Top