Help with index formula with cell references in a closed workbook

[alexv]

I found an article online that works for what i need when I type out the formula, however, I am having issues setting up the formula to read cell values in my existing workbook with the file name.

What I want to accomplish is bringing in certain values of detailed spreadsheets into a master workbook. I thought if my summary included the file name I could write the Index formula pointing to that file name along with the particular cell reference I need. I am stuck. Below is the original article.

How to reference or link value in unopened/closed Excel workbook file?

www.extendoffice.com

In the image the yellow this is the formula written out that gives me the proper result. I am trying to use a concatenation of the file name in C3 along with reference to the column and row numbers so i can do this quickly rather than typing each formula out or doing copy paste as my spreadsheet will have hundreds/thousands of lines.

 

[HongRu]

By only formula, I don't know any way to get values in a closed workbook whick never be opened before.
Workbooks should be opened once to link values even though by VBA (ex: kutools that you refer ).

 

[Fluff]

To use a cell value for the file name, you will need to use the Indirect function, but that will only work if the other workbook is open.