单元格中的 Excel 文件名变量引用

Question

我有一个 excel 公式，它使用GETPIVOTDATA从另一个文件透视中获取值。

我有的公式引用了其他 5 个文件中的 4 个位置，这些文件每月更新一次，并在主文件中汇总它们。

我打开了文件来执行此操作。

我遇到的问题是我必须每月手动更新公式中的文件名，以使它们查看最新文件。

我必须手动更新的当前公式：

=GETPIVOTDATA("Add YTD ",'[FileA P6 2018.xlsm]Monthly report'!$A$5)
+GETPIVOTDATA("Add YTD ",'[FileA P6 2018.xlsm]Monthly sub report'!$A$39)
+GETPIVOTDATA("Add YTD ",'[FileB P6 2018.xlsm]Monthly pivot'!$A$3)
+GETPIVOTDATA("Add YTD ",'[FileC P6 2018.xlsm]Monthly report'!$A$5)
+GETPIVOTDATA("Add YTD ",'[FileD P6 2018.xlsm]Monthly pivot'!$A$3)

下个月文件名会将P6更改为P7.

我有两个单元格，其中包含要在变量中使用的值：

B4 = 6
B5 = 2018

我尝试了以下方法无济于事：

=GETPIVOTDATA("Add YTD ",'[FileA P$B$4 $B$5.xlsm]Monthly report'!$A$5)
+GETPIVOTDATA("Add YTD ",'[FileA P$B$4 $B$5.xlsm]Monthly sub report'!$A$39)
+GETPIVOTDATA("Add YTD ",'[FileB P$B$4 $B$5.xlsm]Monthly pivot'!$A$3)
+GETPIVOTDATA("Add YTD ",'[FileC P$B$4 $B$5.xlsm]Monthly report'!$A$5)
+GETPIVOTDATA("Add YTD ",'[FileD P$B$4 $B$5.xlsm]Monthly pivot'!$A$3)

Answer 1

我认为您将需要间接公式，它使您能够使用变量构建引用。例如，下面使您能够访问工作簿中的单元格 E12

=INDIRECT("[FileA"&B4&B5&".xlsm]Sheet1!$E$12")

我不是 100% 确定您希望如何组合 GETPIVOTDATA - 我认为您可以将 GETPIVOTDATA 调用放在间接调用中，但必须小心转义" ...

Answer 2

此问题已通过VBA和B4单元格设置来解决，并B5：

B4 = P5 2017
B5 = P6 2018

VBA：

Sub FileNameUpdate()
Cells.Replace What:=Range("B4") & ".xlsm", Replacement:=Range("B5") & ".xlsm", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
End Sub