Replace INDIRECT with INDEX to iterate closed workbooks

Question

Yesterday I started a topic in regards of iterating cells from some workbooks into a new, summary workbook. For this I used INDIRECT function:

Iterate between workbooks using INDIRECT

However, realizing the backdrafts of INDIRECT I want to achieve the same thing, but by using INDEX, as I've read from forums that INDEX works even when the referenced workbook is closed, wheras INDIRECT does not.

Then, using

 =INDEX(CONCATENATE("'C:\Pathtofile\[";O282;"]";$O$283;"'!";ADDRESS(11;KOLUMN()-23));1)

Where O282 is the workbook I want to reference (which is referenced in this manner as I will need to iterate some workbooks like the workbook 2015-01 (O282), 2015-02 (P282) etc.) and O283 is the sheet of the corresponding workbook, which in this case is static. I want to import the value of cell D12 in the referenced workbook and worksheet with this, and actually the INDEX does this. ;1 is used to indicate that I only want to return 1 cell. (I believe US excel uses "," instead of ";").

The problem is though that it returns:

 'C:\Pathtofile\[2015-01]Data'!$D$11

so basically. INDEX references correct, but it returns the path and not the value which is intended.

EDIT:

I've tried:

 =INDEX(CONCATENATE("'C:\Pathtofile\[";O282;"]";$O$283;"'!";ADDRESS(11;KOLUMN()-23):ADRESS(12;KOLUMN()-23));1)

to get the Array D12:E12, and then reference to the value at the first referenced row, i.e. D12. This hasn't worked though, but it has been a way to avoid the problems associated with INDEX interpreting my input as a mean to return a reference, rather than a value.

Index works even when the referenced workbook is closed? That's not true. It will just display you the cached value. Should the source workbook get changed, you will not see the changes in the destination until you refresh workbook connections (which is in fact re-opening the referenced file). — ttaaoossuuuu
If I understand this correct. The referenced files are updated manually by adding the new monthly data "by hand" by a coworker. The Index function in the destination file will then update automatically? Or do I need to personally open the files for the destination file to update? Do you know of any other function which can do what I sought after? — Cenderze
The value in the destination file will be updated only if both source and destination files are open. Then if you save the destination file, the new value will be cached and displayed next time you open the file (even if the source file is closed at that moment). If you need to fetch info without having to open the source file, it can be done only via VBA. — ttaaoossuuuu
Oh then I totally misunderstood INDEX. But then again, merely open the files once a month must be considered doable as it is so few files to open, so I'll continue with INDEX. As long as I don't need to have them open every time I check the summary file. Do you have any idea as to why the formula only returns the path? — Cenderze

ttaaoossuuuu ttaaoossuuuu · Accepted Answer · 2015-03-11T12:14:59

INDEX, in your case, takes two arguments: reference and position. Instead of reference, you specified address, so Excel understands it as range with one value in it - which is your address and returns it to you. You should:

=INDEX(INDIRECT(CONCATENATE(...));1)

But as you use the first value in referenced range, you can simply do:

=INDIRECT(CONCATENATE(...))

Replace INDIRECT with INDEX to iterate closed workbooks

1 Answers