Avatar of daviddiebel
daviddiebel
 asked on

Alternative to PULL or INDIRECT.EXT for referencing closed workbooks?

I am trying to use indirect reference to a large number of closed Excel workbooks. I am working in Excel 2007. In Excel 2003, MoreFunc allows use of the INDIRECT.EXT function, which pretty quickly allows reference to a large number of closed workbooks.

For Excel 2007, there is the alternative of code for the "PULL" function, that can substitute for INDIRECT.EXT in the reference formulas; however, PULL is very, very slow, making it impractical for references many hundreds of workbooks.

Are there any alternative means for using an INDIRECT-like function to reference closed workbooks?  The workbook in which I'm working has all of the workbook location in columnA , all the file names in column B, and the relevant worksheet name in column C.  In cell D1, I list the cell reference to be refernce, and in cells D2.....Di, I have the formulas for indirect reference - but I cannot find a practical solution!

I attached an example of what I'm working with, but need an alternative to the INDIRECT.EXT formulas.

You assistance is greatly appreciated!
Database-05.23.12.xls
Microsoft ExcelVisual Basic ClassicVB Script

Avatar of undefined
Last Comment
M Zahid

8/22/2022 - Mon
byundt

One approach would be to create a set of "master" formulas that use placeholders for the path, file name and sheet name. A VBA macro would then use those master formulas and replace the placeholders (on a row by row basis) with the data in columns A:C. You would then have worksheet formulas pointing to the external workbooks without need for INDIRECT. The macro could even replace the formulas with the values returned afterwards, should you like.
Rob Brockett

This is not a function, but if you are only interested in the values from each file, then you could use Ron DeBruin's free Merge Addin which will let you merge all the data from multiple files into a single sheet. This is illustrated & available from his site: http://www.rondebruin.nl/merge.htm

I've used the addin a few times & I think it is user friendly, flexible and reasonably fast, but I haven't used morefunc for 4 or 5 years so I haven't got a recent speed comparison.

hth
Rob
ASKER CERTIFIED SOLUTION
byundt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
daviddiebel

ASKER
This is a great solution - thank you!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Confused Still

Can you share an example of this working? I'm trying something similar and can't seem to get the macro to work correctly.
byundt

Confused Still,
If you have trouble with implementation, please post your workbook--including your failed attempt to make it work.

I'd be glad to install the macro in it and make sure you have "template formulas" that match up to its requirements.

Brad
Confused Still

I figured it out. I need the template formula to be '='Path[Book]Sheet'!D$2
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
M Zahid

HI Brad,
seem like its nice solution but will it work with the network path as well e.g.
I have Summary Report linked with the 3 different files from the network & different folders, that summary report is like a template to extract the summary as per the new projects

(unable to put screen shot of my master sheet)

below are from my master sheet which I linked with 3 hyperlinks

To get the new file name formula been used is ('=$D$3&" - "&$D$4&" "&D9&" Rev "&$D$5&".xlsb") which reads from the above cells form same sheet

To extract the data this the hyperlink =HYPERLINK("\\share\Price Form"&$D$6&""&$D$3&" - "&$D$4&""&"REV "&$D$5&""&E9,"Link")

This is the data I need from the above links =[&$E11&]Report 1'!$G$48

Everything is working fine, I would like to do this hyperlinks to be update with one click or automatically & without opening the source files, sometime those files to big or slow network causes delay.

your prompt response would be appreciated
byundt

The suggested code in this thread will build any kind of Excel formula you need as long as you write it with placeholder text where you need to substitute in the Path, Filename, and SheetName. You will need to modify the code to match your worksheet layout. You could then call the sub (macro) that produces the HYPERLINK formulas with a Forms control button click.

I have posted this approach several times as the answer to Experts Exchange questions. I have also used it in my day job to avoid the need for INDIRECT functions. I am confident the approach will work for your problem, but if you have trouble implementing it, I suggest that you open a new question and reference this one.
M Zahid

i hv posted new question as advice
Your help has saved me hundreds of hours of internet surfing.
fblack61