Index/Match to dynamic path closed workbook - NO VBA/ADD-IN

Hi,
I am trying to use Index/Match function with a dynamic path referring to external CLOSED workbooks. I need a dynamic path because I have several workbooks with different names.

What I am struggling with is the dynamic part of the formula as it works when I use Index/Match with a hard coded path.

I have already tried to combine Index and Indirect, but I later realized that Indirect dose not work with closed workbooks.

I have tried also the INDEX(CONCATENATE version, but I still get the "VALUE" error.

I will try to give you an example here below.

Folder path where all the workbooks are stored:
Q:\Models

File names of the different workbooks:
A_Model
B_Model
C_Model
D_Model

Now, if I use INDEX(array, row_num, [column_num]) it works

Example:
=INDEX('Q:\Models\[A_Model.xlsm]Model'!$A:$E,row_num,[column_num])
=INDEX('Q:\Models\[B_Model.xlsm]Model'!$A:$E,row_num,[column_num])
=INDEX('Q:\Models\[C_Model.xlsm]Model'!$A:$E,row_num,[column_num])
=INDEX('Q:\Models\[D_Model.xlsm]Model'!$A:$E,row_num,[column_num])
=INDEX('Q:\Models\[E_Model.xlsm]Model'!$A:$E,row_num,[column_num])

Now let's suppose I want to have dynamic links such that the file name (A,B,C,D etc..) changes dynamically.

Let's say I put all the file name in cells A1:A5

A1=A
A2=B
A3=C
A4=D
A5=E

and now I combine INDEX and CONCATENATE so to achieve a dynamic patch.

=INDEX(CONCATENATE("'Q:\Models\[",A1,"_Model.xlsm]Model'!$A:$E"),row_num,[column_num])
=INDEX(CONCATENATE("'Q:\Models\[",A2,"_Model.xlsm]Model'!$A:$E"),row_num,[column_num])
=INDEX(CONCATENATE("'Q:\Models\[",A3,"_Model.xlsm]Model'!$A:$E"),row_num,[column_num])
=INDEX(CONCATENATE("'Q:\Models\[",A4,"_Model.xlsm]Model'!$A:$E"),row_num,[column_num])
=INDEX(CONCATENATE("'Q:\Models\[",A5,"_Model.xlsm]Model'!$A:$E"),row_num,[column_num])

This should work as the new path is a result of a concatenation, however I get the error VALUE

Can anyone help me out with this?

Thank you

I'm 99.9% certain that you can't get this to work without VBA. I am equally 100% certain that it will work WITH VBA.

Why do you NOT want VBA, when your sheets are macro-enabled (.xlsm)?

Rustiness isn't a problem. Plagiarism is the solution!!!

The code you need is in Module 1 of "Pull Lookup". But first... try it out using the two sample files here. This is set up for a simple vlookup, but you can adapt this, I'm sure...

Download both files (e.g. to Desktop). open Pull lookup, enabling macros. Change the path in C2 to suit your setup. Voila... (hopefully).

Originally Posted by rcatchlove

Trying to use the Pull and Source files with CSV data. Do you think that would work? Thanks

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread