在使用INDIRECT的工作簿之间迭代 [英] Iterate between workbooks using INDIRECT

问题描述

我想创建一个汇总文件，我想从单元格D11从一些工作簿中导入数据。为此，我尝试使用INDIRECT调用工作簿O284，其中O284至O296由以下字符串组成：

I want to create a summary file where I wish to import the data from cell D11 from some workbooks. For this I try to use INDIRECT to call the workbook O284, where O284 through O296 consists of strings like:

 2015-01 (for O284), 2015-02 (for O285 etc)

其中O285是sheetname，每个工作簿一致，因此是$$。

and where O285 is the sheetname, which is consistent for each workbook hence the $$'s.

=INDIRECT("'C:\Path\["&O284&"]"&$O$285&"'!$D$11")

然而，运行它不会返回值，一个 ＃＃＃＃。我已经打开所有的工作簿。我怀疑我的INDIRECT代码片段出错，但我不知道如何。有人有线索吗？

Running this however does not return a value, but merely a ####. I have all the workbooks opened. I suspect I conduct the INDIRECT snippet wrong, but I can't figure out how. Does anybody have a clue?

问候，

Btw，有没有办法实现同样的事情与INDIRECT一起使用封闭的工作簿，而不使用宏？像粘贴值一样，您可以使用与INDIRECT相同的方法，但返回数字，而单元格中没有公式符号？

Btw, is there a way to achieve the same thing as with INDIRECT that work with closed workbooks without using macros? Like a "paste value" or something which you can use in the same way as INDIRECT but that returns a digit, without the formula notation in the cell?

编辑：

=INDIRECT(CONCATENATE("'C:\path\[";O282;"]";$O$283;"'!D11"))

用于从工作簿O282获取值D11。当我拖动此公式时，下一列将引用工作簿P282。然而，它仍然得到单元格D11。我想要从这个工作簿中获取E11单元格，并且这样工作。有没有人有任何想法？

works for getting the value "D11" from the workbook O282. When I drag this formula out, the next column will reference to workbook P282. However, it still get cell D11. I want it to get the E11 cell from this workbook, and work like this. Does anyone have any idea?

推荐答案

=INDIRECT(CONCATENATE("'C:\";[@Path];"\[";[@WorkbookName];"]";[@SheetName];"'!";ADDRESS(11;COLUMN() - 11)))

我认为代码是你想要的。

I think up code is something that can you want.

• 显示 #### 有时是因为单元格宽度。

• Showing #### is sometimes because of cell width.

这篇关于在使用INDIRECT的工作簿之间迭代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！