excel - 索引/匹配公式中的动态工作表名称

Question

背景

美国政府发布我用于工作的每日报告。最近，政府改变了他们发布报告的方式。他们不再以文本文件的形式发布报告，而是以 pdf 格式发布。不幸的是，这影响了我从报告中提取信息的方式。以前，我从站点中提取信息，因为它被保存为文本文件。现在，我下载 pdf 并将其转换为 excel 文件。

我有 1 个工作簿，其中包含单个工作表上已保存的报告副本，例如4 月 30 日、4 月 29 日、4 月 28 日等。不幸的是，由于转换，每个页面上的信息可能会发生一两行的变化，例如4 月 30 日工作表上的 Information_A 可能位于第 30 行，而 4 月 29 日它可能位于第 33 行。

在工作簿的第一页上，我有一个摘要页面，它从上一页中提取关键信息，例如摘要将从 4 月 30 日的工作表中提取信息。

问题

现在的问题来自我如何从 excel 文件中提取信息。由于信息不在同一行，我认为 Index/Match方法最好。我做的公式如下。引用名称 Information_A 在 A 列中，而我需要的 Information_A 值在 P 列中。
=(INDEX('April 30'!P:P,MATCH("Information_A",'April 30'!A:A,0)))
由于我的工作表不断变化，我认为我可以提取最后一个工作表名称，然后将其输入到公式中，如下所示，
=(INDEX('[previous_sheet]'P:P,MATCH("Information_A",'[previous_sheet]'!A:A,0)))
所以我试图弄清楚如何提取以前的工作表名称。我尝试了几种不同的方法，最终使用了 ChrisB 的解决方案 here .

我已将他提供的公式(如下)保存为 PrevSheet_2
=IF( MATCH(wsName,wsNamesArray,0)-1 =0, ERROR.TYPE(7), INDEX(wsNamesArray,MATCH(wsName,wsNamesArray,0)+1))
其中，wsNamesArray和wsName分别如下，

wsNamesArray: =RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1))) & T(NOW())
wsName: =MID(CELL("filename", INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,255) & T(NOW())
这导致了“最终”公式，
=(INDEX("'"&PrevSheet_2&"'!P:P",MATCH(Information_A","'"&PrevSheet_2&"'!A:A",0)))
这是行不通的。这个公式给了我一个#VALUE!错误。

我在没有使用 PrevSheet_2 的情况下测试了公式并输入了工作表名称，例如4 月 30 日，得到了正确的值，所以我知道该部分正在工作。我还在一个单元格中测试 PrevSheet_2 并获得了正确的前一张表，所以我知道问题出在 2 的组合上。

那么，我的问题是如何制作这个
=(INDEX("'"&PrevSheet_2&"'!P:P",MATCH(Information_A","'"&PrevSheet_2&"'!A:A",0)))
公式工作。

我感觉 PrevSheet_2 的数据类型错误，但我不知道如何修复它。

编辑:

我也尝试过使用 INDIRECT(如下)并获得了#REF!错误。
=INDEX(INDIRECT("''"&PrevSheet_2 &"'!P:P"),MATCH("Information_A",INDIRECT("'"&PrevSheet_2&"'!A:A"),0))

Answer 1

引用 this link .. 将 SheetNames 数组(在“公式”>“定义名称”中)定义为 SheetNames = MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1),1)+1,LEN(GET.WORKBOOK(1))) .. 然后在 excel 公式中，您可以将以前的工作表名称作为 INDEX(SheetNames,SHEET()-1)
您也可以定义PreviousSheetName = INDEX(MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1),1)+1,LEN(GET.WORKBOOK(1))),SHEET()-1)
您可以按照@BigBen 的建议将其放入 INDIRECT 函数中

所以公式将是 =INDEX(INDIRECT("'"&PreviousSheetName &"'!P:P"),MATCH("Information_A",INDIRECT("'"&PreviousSheetName&"'!A:A"),0))
见.. Sheet函数为我们提供当前工作表的索引。所以Sheet()-1给我们上一张表的索引

在我回答之后，我注意到您在间接公式中添加了 TWICE 单引号。因此，#REF 错误