Concantenated links to workbooks

[Unicornus]

Hi everyone, I tried a simple search on forums but no answer to my problem poped up. I got this situation:

Got circa 20 workbooks created as output form db sotware, so formatting is exactly the same in all of them. Each workbook conains multiple sheets. I Need output in single workbook, with certain data outsourced from different sheets and different workbooks. I know this could have been accomplished with vba scripting, but I'm not familiar with that and also other reason I chose to link the data between the workbooks, but here comes the problem:

The links were put together with concantenate function

=CONCATENATE("='D:\Documents\NAM\2014\inventory\[";C$1;".xls]";Source!$A2;"'!";Source!$D2;Source!$C2)
-------------------------------------file location---------------filename-----sheetname-----cell coordinates--------

This example is just for one sheet in the output workbook (other sheets have different and sometimes a little more complicated script) The command deliver correctly scripted link but the link does not function, it only activates itself upon click ino the formula bar (or hitting key "F2") and pressing "ENTER". Only then the values apear instead of the link script.

Of course it is not possible to manually "hit ENTER" upon twenty thousands cells. Does anyone have a solution for this i'd be really grateful for any sensible solution.

 

[steve the fish]

Hi..

Indirect would work. Either pointing to the concatenate or indirect(concatenate())

 

[steve the fish]

Without the '=' inside the concatenate

 

[Unicornus]

thx Steve for the reply, nonetheless i had to sovle the problem differently, the cells would not "load" data from closed workbooks, if they were opened previously it all worked.