Iterate between workbooks using INDIRECT

Question

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)

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

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

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?

Regards,

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?

EDIT:

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

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?

Answer 1

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