Hi all,
I have scoured through countless posts hoping to find a solution, but I have failed. The problem seems to be that concatenate adds "" around the table_array which messes it up.
My goal is to search a specified excel file for the yesterday's date. It will be in the 'E' column in the format of 'YYYY-MM-DD HH:MM:SS'.
From my excel file I have a column of server shares and file names that must be passed to a vba function to get find the specific file name (as they may have the seconds on them).
My example looks like this:
<tbody>
</tbody>
I have to call a macro getfilename(A2,B2) to have it find the specific file name.
I tried to put this in the VLookup and also putting it into a cell I reference later...neither worked
so I have concatenate("'",A2,"[",getfilename(a2,b2),"]","Report 1'!$E:$E") to make my table_array
The rest is VLOOKUP(TEXT(TODAY()-1,"yyyy-mm-dd")&"*",table_array,1,FALSE))
I am losing hair from all the head scratching trying to figure this one out, so hopefully someone can help!!
Thank you in advance!
I have scoured through countless posts hoping to find a solution, but I have failed. The problem seems to be that concatenate adds "" around the table_array which messes it up.
My goal is to search a specified excel file for the yesterday's date. It will be in the 'E' column in the format of 'YYYY-MM-DD HH:MM:SS'.
From my excel file I have a column of server shares and file names that must be passed to a vba function to get find the specific file name (as they may have the seconds on them).
My example looks like this:
| Server Path (Column A) | File Name+WildCard (Column B) |
| \\appdata-eta\ttt\bbb_daily\dcd | NCMDFJASD2017-12-11*.xlsx |
| \\appdata-eta\ttt\bbb_daily\dcd\Archive | NASDASD2017-12-11*.xlsx |
<tbody>
</tbody>
I have to call a macro getfilename(A2,B2) to have it find the specific file name.
I tried to put this in the VLookup and also putting it into a cell I reference later...neither worked
so I have concatenate("'",A2,"[",getfilename(a2,b2),"]","Report 1'!$E:$E") to make my table_array
The rest is VLOOKUP(TEXT(TODAY()-1,"yyyy-mm-dd")&"*",table_array,1,FALSE))
I am losing hair from all the head scratching trying to figure this one out, so hopefully someone can help!!
Thank you in advance!
