Vlookup to a sheet in a workbook that changes each month

[AccountantHarry]

Hi everyone

I have learnt so many tricks from this forum which has made my job much easier. I have another "challenge" now:

I have inheriated a workbook with many cells lookup to a worksheet in another workbook, it is simply, however, the worksheet changes each month, for example, in Oct the formula is :
=Vlookup(A3,W:\finance\2019\monthend\[Fixed Costs data.xls]Oct 19'!$L3:$M200,2,false)

Next month in Nov it will become:

=Vlookup(A3,W:\finance\2019\monthend\[Fixed Costs data.xls]Nov 19'!$L3:$M200,2,false)

At present, I use "find and replace" to change the formula, I wonder if there is a way that I can make it dynamic, perhaps based on a cell somewhere.

Thanks in advance
​

 

[Special-K99]

Try (untested)

=Vlookup(A3,INDIRECT("W:\finance" & YEAR(A1) & "\monthend\[Fixed Costs data.xls]" & TEXT(A1,"mmm yy") & "'!$L3:$M200"),2,false)

where A1 is "Nov 19" or similar

 

[AccountantHarry]

Brilliant, thank you ?