Excel公式，使用vlookup时会自动更改文件名的日期 [英] Excel formula that automatically changes the date for a filename when using a vlookup

问题描述

示例:

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

名为批准的承包商08302018.xlsx"的电子表格的文件名将在下周更改为批准的承包商09062018.xlsx".电子表格中的选项卡名称称为带键承包商".

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".

当前，我必须进入公式，在文件名之后更新日期，然后将其粘贴到excel中，然后将其复制到所有单元格中.如何获得我得到它自动更新.我当时在想=today()-8，但是我不确定如何在vlookup中为文件名做这件事?

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?

推荐答案

假设:

1..您只能更改文件名中的日期(例如，仅08302018部分)

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

2..此日期始终设置为mmddyyyy

2. This date is always formatted as mmddyyyy

3..此日期始终为=TODAY()-8

4..所有其他详细信息保持不变(例如，工作表名称，源文件与目标文件位于同一文件夹，C列下的返回值等)

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...)

然后，您可以使用INDIRECT函数在公式中自动更改该日期:

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)

但是，我注意到您的示例违反了第三个假设，并且您希望将其引用到当前周的THURSDAY日期，而不是引用它来引用TODAY()-8.在这种情况下，您只需要在上述公式中更改参考日期即可.请考虑以下公式:

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)

为澄清起见，它的作用是将公式中引用文件名中的日期更改为当前星期的THURSDAY日期.因此，当您在今天(2018年9月15日)在文件中运行该文件时，参考文件名应为批准的承包商09132018.xlsx" .

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".

如果返回错误，则可能意味着假定的参考文件名称不正确.在这种情况下，使用IFERROR函数将提醒我们检查参考文件名.当确实发生错误时，以下公式将显示请检查引用文件名" :

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")

这篇关于Excel公式，使用vlookup时会自动更改文件名的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！