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

daviddiebel
daviddiebel used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Here is a macro that does what I am talking about. It assumes you have template formulas in D1:H1, and a table with path, filename & sheet name in A4:Cxx. The template formula looks like this:
'='Path[Book]Sheet'!D$2

The macro replaces the values for the placeholders Path, Book and Sheet with the data in your workbook, creates the resulting formula and lets it return values.

Sub GetIndirectData()
Dim rgFrmla As Range, rgData As Range, rgResults As Range
Dim i As Long, j As Long, nCols As Long, nRows As Long
Dim frmla As String
Application.ScreenUpdating = False
With ActiveSheet
    Set rgFrmla = .Range("D1:H1")    'Formula "templates" are stored here
    Set rgData = .Range("A4")        'Top left cell containing path info
    Set rgData = .Range(rgData, .Cells(.Rows.Count, rgData.Column).End(xlUp)) 'All the paths
    Set rgData = rgData.Resize(, 3) 'All the paths, filenames & sheet names
    Set rgResults = Intersect(rgData.EntireRow, rgFrmla.EntireColumn)
End With
nCols = rgFrmla.Columns.Count
nRows = rgData.Rows.Count
For i = 1 To nRows
    For j = 1 To nCols
        frmla = rgFrmla.Cells(i, j).Value
        If Left(frmla, 1) = "'" Then frmla = Mid(frmla, 2)
        rgResults.Cells(i, j).Formula = Replace(Replace(Replace(frmla, "Path", rgData.Cells(i, 1).Value), _
                "Book", rgData.Cells(i, 2).Value), _
                "Sheet", rgData.Cells(i, 3).Value)
    Next
Next
End Sub

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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.
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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

Commented:
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
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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.

Commented:
i hv posted new question as advice

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial