引用从单元格获取文件名的Excel文件

I want to get values from different excel files (say, File-AAA.xlsx, File-ABC.xlsx, etc). ¿How can I reference said files without inputting manually the "ABC" part of the filename, but getting it from the current sheet instead?

我想从不同的excel文件中获取值(例如,File-AAA.xlsx,File-ABC.xlsx等)。 ¿如何在不手动输入文件名的“ABC”部分的情况下引用所述文件,而是从当前工作表中获取它?

In the example, from our current sheet we want to get the values of A1 and A2, from the File-ABC.xlsx file, and sum them.

在示例中,从我们当前的工作表中,我们希望从File-ABC.xlsx文件中获取A1和A2的值,并对它们求和。

For instance, in B2, instead of writing:

例如,在B2中,而不是写:

    =SUM('\\server\path\[File-ABC.xlsx]Data'!A1:A2)

I would like to be able to write it as

我希望能够把它写成

    =SUM('\\server\path\[File-*REFERENCE TO CELL*.xlsx]Data'!A1:A2)

Where *REFERENCE TO CELL* is the sought text string that references the cell that contains "ABC" (A2).

其中*参考CELL *是引用包含“ABC”(A2)的单元格的搜索文本字符串。

2 个解决方案

#1

You can use the indirect function for this. Something like this -

您可以使用间接功能。像这样的东西 -

=SUM(INDIRECT("'\\server\path\[File-"&A2&".xlsx]Data'!A1"&":"&"A2"))

Edit-1 : You can use the same syntax to refer to the local directory that you want, just replace \server\path[File- with the path of the new directory. You can copy the path from your file explorer after browsing to the concerned folder.

编辑1:您可以使用相同的语法来引用所需的本地目录,只需将\ server \ path [File-替换为新目录的路径。浏览到相关文件夹后,您可以从文件资源管理器中复制路径。

Eg. Taking the path from your comment (after correction):

例如。从你的评论中取道(修正后):

=SUM(INDIRECT("'C:\Users\PeteThePanda\Spreadsheets[File-"&A3&".xlsx]Data'!A1"&‌":"&"A2"))

#2

A more straightforward way to do this is set up a conditional statement outside the sum, e.g. if the value of A2 in the current workbook is ABC, then go to this path and bring in the sum.

更直接的方法是在总和之外设置一个条件语句,例如:如果当前工作簿中A2的值为ABC,则转到此路径并输入总和。

e.g.

=if(A2="ABC",sum('\\server\path\[File-ABC.xlsx]Data'!A1:A2),if(A2="AAA", sum('\\server\path\[File-AAA.xlsx]Data'!A1:A2),"No File"))

= if(A2 =“ABC”,sum('\\ server \ path \ [File-ABC.xlsx] Data'!A1:A2),if(A2 =“AAA”,sum('\\ server \ path \ [File-AAA.xlsx]数据'!A1:A2),“无文件”))

Note, you can add additional nested IFs if there are more files. Just start a new if where I wrote "No File".

注意,如果有更多文件,您可以添加其他嵌套IF。如果我写了“No File”,就开始新的。

#1