Indirect alternative for external workbook

[jimmisavage]

Hi, I'm struggling with an alternative here.

Cells A3:A50 has a list of workbook names in a specific location (this auto updates when a workbook is added or removed) which is where i want to pull my data from - from sheet 2 column B. Each workbook is of the same layout, which makes it a little easier.

For ease of explanation lets say I have Book1.xlsx, Book2.xlsx and Book3.xlsx saved at C://documents/test and I want to count the number of 'x''s in cell B15 and show the result in cell C1.
The tricky part is the number of files in the folder will change so it needs to be a dynamic formula that always looks in every file in the folder.

*an indirect formula will work but obviously requires all workbooks to be open - this is not possible, as there would be 100's of workbooks in the folder.
**also, we're using excel 2007 so i can't use a power query and VBA isn't a viable option.

Any help would be appreciated.

 

[James006]

Hello,

In my opinion, the best alternative is the solution designed by John Walkenbach for getting Value from a closed workbook...

Function GetValue(path, file, sheet, ref)
'   Retrieves a value from a closed workbook
     Dim arg As String
'   Make sure the file exists
     If Right(path, 1) <> "\" Then path = path & "\"
     If Dir(path & file) = "" Then
        GetValue = "File Not Found
        Exit Function
     End If
'   Create the argument
     arg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)
'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function

Hope this will help

 

[jimmisavage]

Hi James,
Thanks for the quick response!
I'm trying to find a solution without a macro/vba because of the computer illiterate nature of where I work. People are too scared of things they don't understand!

 

[jimmisavage]

I'm hoping/wondering if there is a way to achieve my goal by using the Define Names function?
I've managed to get the full file location for each individual file in cells C3:C50 - now i was hoping to use that location in a formula in D3:D50... maybe with a Define Name?

 

[James006]

Hello,

It is a UDF function ...in fact a function similar to =Sum() ...

Users only need to input =GetValue()

Hope this clarifies

 

[jimmisavage]

Hello,

It is a UDF function ...in fact a function similar to =Sum() ...

Users only need to input =GetValue()

Hope this clarifies

HiJames,
What i mean is when users see the 'enable macro' popup they tend to panic. It's not worth the stress - I've tried before!

 

[Swayzy]

Just use power query's get data function. Having your data in a real formal table is the only requirment for it to work. I can promise that learning both power query and power pivot will completely change your way of working with spread out data.

If you can reliably find the File paths then the simplest thing is to use vba to hardcode in thoose paths directly into range formulas

 

[jasonb75]

You can only do what you want with the methods that you have already dismissed, there is no other alternative.

To do it with a defined name would still need INDIRECT so you're back to square 1.

 

[James006]

HiJames,
What i mean is when users see the 'enable macro' popup they tend to panic. It's not worth the stress - I've tried before!

If this is your only problem ... you can have it turned on without the users' intervention ...

 

[jimmisavage]

If this is your only problem ... you can have it turned on without the users' intervention ...

I didn't know that was an option! Anyway, if I'm using this code, how do I use it with variables?
The folder location is written in cell A2.
The variable file names are written in A3 to A50.
The Sheet is always Sheet2.
The cell is... Actually, i need to populate a table. So the cell is B2, B3, B4... up to B18.

Where do I put the =GetValue() formula?
Sorry, I'm very confused about what this is doing.