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

Active Member
Joined
Aug 28, 2016
Messages
436
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,685
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,078,137
Messages
5,338,458
Members
399,234
Latest member
WaddoAU

Some videos you may like

This Week's Hot Topics

Top