I have to insert the VLOOKUP formula to sum up specific columns from the newest file in a folder.
To get the newest file I used:
outfilepath=Directory.GetFiles(FolderPath,"*.xlsx”,SearchOption.AllDirectories).OrderByDescending(Function(d) New FileInfo(d).CreationTime)(0)
Then I wrote the formula into a txt file and replaced the field < C1>, C2 with the cells I need.
=IFERROR(VLOOKUP($B< C1>,’< C3>’!$A:$N,< C2>$2,0),0)
I replaced C3 with outfilepath to insert the newest file path.
Until now the formula in excel looks like this:
=IFERROR(VLOOKUP($B4;‘C:\Users\admin\Box[Test file WK 35.xlsx]’!$A:$N;G$2;0);0)
But to get the data I need I, the formula also needs the sheetname (“output”)
But if I just insert the word “Output” right after the < C3> in the txt then the formula looks like this in Excel:
=IFERROR(VLOOKUP($B4;‘C:\Users\admin\Box[Test file WK 35.xlsxOutput]Test file WK 30’!$A:$N;G$2;0);0)
And that’s wrong.
Does anyone know how I can handle this problem?