Vlookup with file path in table array

[excelhelp9876]

Hi, this is the vlookup I currently have (that works)

VLOOKUP(F2,'\\documents\August\[File Name 30.08.22.xlsm]Sheet 1'!$F:$Z,21,0)

The issue is that everyday I have to reset the formula with the prior working day so that it's pulling through the latest comments. I'm trying to create a static formula that will automatically lookup to the prior days sheet.

How do I get the File name to be yesterday's date. I've got - =WORKDAY(TODAY(),-1) but I can't get this into the vlookup without it erroring.

Any help is really appreciated!

 

[offthelip]

welcome to forum, try this:

Sub test3()
Dim dd As String
Dim mm As String
Dim yy As String

'VLOOKUP(F2,'\\documents\August\[File Name 30.08.22.xlsm]Sheet 1'!$F:$Z,21,0)
firstbit = "VLOOKUP(F2,'\\documents\August\[File Name "
secondbit = ".xlsm]Sheet 1'!$F:$Z,21,0)"
tt = (Now() - 1)
dd = Day(tt)
If Len(dd) < 2 Then dd = "0" & dd
mm = Month(tt)
If Len(mm) < 2 Then mm = "0" & mm
yy = Year(tt) - 2000
dt = dd & "." & mm & "." & yy

formul = firstbit & dt & secondbit
MsgBox formul

End Sub

 

[Anthony47]

Offthelip's code would already solve the question, nevertheless let me suggest this variant:

Dim FirstBit As String, fBit2 As String, SecondBit As String, MidBit As String
Dim preDate As Date, nForm As String
'
FirstBit = "=VLOOKUP(F2,'\\documents\"
fBit2 = "\[File Name "
SecondBit = ".xlsm]Sheet 1'!$F:$Z,21,0)"
preDate = Application.WorksheetFunction.WorkDay(Date, -1)
MidBit = Format(preDate, "mmmm") & fBit2 & Format(preDate, "dd.mm.yy")
nForm = FirstBit & MidBit & SecondBit

'MsgBox nForm
Range("XY").Formula = nForm        'Your Range

This uses WORKDAY to determine the previous date and use Format to create the strings that fit in the new formula
Also, I assume that "August" in the formula will soon become "September", then I modified also that portion of the path

Try...