-1

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?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
John
  • 3
  • 1
  • 2
  • 1
    How many of these are there? Do you want to use a volatile worksheet function on all of them? Is the external workbook always open? Does the sequence ever change due to holiday or other? –  Sep 14 '18 at 21:20
  • The dates will always be today's date? It is not clear how this date change occurs. – Dalton Cézane Sep 14 '18 at 21:25

1 Answers1

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")
J.Mapz
  • 511
  • 2
  • 12
  • I will double check this on Friday and leave a more detailed response. I did a quick test and it seems to work. Thank you! – John Sep 17 '18 at 20:23
  • So the one that worked the best was the first vlookup formula you provided. =VLOOKUP(B2,INDIRECT("'[Approved Contractors "&TEXT(TODAY()-8,"mmddyyyy")&".xlsx]Contractor with key'!$B$2:$C$99999"),2,0) If there is a Friday where we can't run this for some reason I can mess with it at that time but this is perfect for now. Thank you! – John Sep 21 '18 at 17:56