Link to home
Start Free TrialLog in
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
Avatar of byundt
byundt
Flag of United States of America image

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.
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
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of daviddiebel
daviddiebel

ASKER

This is a great solution - thank you!
Can you share an example of this working? I'm trying something similar and can't seem to get the macro to work correctly.
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
I figured it out. I need the template formula to be '='Path[Book]Sheet'!D$2
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
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.
i hv posted new question as advice