Iterate between workbooks using INDIRECT

1
votes

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?

1
excelexcel-formula
Hmm.. trying to understand, where do you need to use CONCATINATE? – Cenderze
Pardon me, I'm out of your question, I'm deleting my comments. Showing #### is sometimes because of cell width, that I think you actually know this. – shA.t
It was not due to cell width, but your comment actually works. I'm now trying to make it work so that "D11" does not use $$, and instead can be dragged out to E11, F11 etc. Havent gotten it to work yet by simply removing the $$'s or by changing some " '. – Cenderze
In Office support page it seems you don't need $$, but when you make a reference by clicking it add $$. But Actually I suggest using ADDRESS(11,4) instead of D11, this will help you in changing columns ;). – shA.t
I add an answer by using column() function to change D to E – shA.t

1 Answers

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