vba - Excel formula that automatically changes the date for a filename when using a vlookup

Question

Example:

=VLOOKUP(B2,'[Approved Contractors 08302018.xlsx]Contractor with key'!$B$2:$C$99999,2,0)

The file name for the spreadsheet named "Approved Contractors 08302018.xlsx" will change next week to "Approved Contractors 09062018.xlsx". The tab name in the spreadsheet is called "Contractor with key".

Currently, I have to go into the formula, update the date after the file name, then paste it into excel, then copy it down to all the cells. How to get I get it to update automatically. I was thinking =today()-8 but I'm not sure how to do it inside a vlookup for a file name?

Answer 1

Assuming that:

1. You only change the date in the filename (ex. only the 08302018 part)

2. This date is always formatted as mmddyyyy

3. This date is always =TODAY()-8

4. All other details remain the same (e.g worksheet name, source file being in the same folder as destination file, return values under column C, etc...)

Then you can use the INDIRECT function to automatically change that date within the formula:

=VLOOKUP(B2,INDIRECT("'[Approved Contractors "&TEXT(TODAY()-8,"mmddyyyy")&".xlsx]Contractor with key'!$B$2:$C$99999"),2,0)

However, I noticed that your example goes against the 3rd assumption, and that instead of referencing it to TODAY()-8, you want to reference it to the THURSDAY date of that current week. In that case, you just need to change the reference date in the above formula. Kindly consider the formula below:

=VLOOKUP(B2,INDIRECT("'[Approved Contractors "&TEXT(TODAY()-WEEKDAY(TODAY())+5,"mmddyyyy")&".xlsx]Contractor with key'!$B$2:$C$99999"),2,0)

To clarify, what this does is it changes the date in reference filename within the formula to the THURSDAY date of the current week. So when you run it in your file today (9/15/2018), the reference filename should be "Approved Contractors 09132018.xlsx".

If this returns an error, it may mean the supposed reference file was incorrectly named. In this case using an IFERROR function would alert us to check the reference filename. When an error does happen, the following formula would display "Please check reference filename":

=IFERROR(VLOOKUP(B2,INDIRECT("'[Approved Contractors "&TEXT(TODAY()-WEEKDAY(TODAY())+5,"mmddyyyy")&".xlsx]Contractor with key'!$B$2:$C$99999"),2,0),"Please check reference filename")