Rework of an INDIRECT.EXT function

A weekly file is created with a scoring method for colleagues
I then had a monthly master file that by referencing the months “week numbers” the data from the required 4 or 5 workbooks could be combined for a months’ total (and a copy saved called that month)
This worked perfectly with Excel 2007 and INDIRECT.EXT function from MOREFUNC
Having had to move to excel 2016 this is the one major loss have I have encountered

Can anyone suggest an alternative method

Formula was

Formula:

Please Login or Register  to view this content.

C3 = p:\my Documents\driver database\driver assessment 2016\ (Path)
D3 = DRIVER ASSESSMENT WK_27.xlsm ‘This is the variable’ (Workbook Name)
I3 = SCALESHEET (Worksheet Name)

Scale = named range
Drivername = named range
Score = named range
Title = named range

Any help appreciated...

Yes there is a solution. There's a UDF called "Pull" that enables you to do this. I haven't downloaded your files, as I have had occasional bad experiences with macro-enabled files pulled down from the interweb.

That said, I am now uploading a macro enabled file that you'll probably have to open...

The syntax id simple enough:

=VLOOKUP(B2,pull("'C:\Users\Dglenn\Desktop\["&$A$2&".xlsx]Sheet1'!a1:b26"),2,FALSE)

is a sample VLOOKUP. so open the file, or copy the code into a Module. You'll have to change the path in the formula to suit your situation... obviously.

Please Login or Register  to view this content.

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

Although the thread is closed, I restored the original post so that the response makes sense, and moved the thread from Excel General since the OP did not open a duplicate thread but indicated that it should be moved.