referencing a file from a value in a cell

immyjimmy · Jan 6, 2005

Greetings all,

I have a "To Do" list in a workbook that has a sales number in column A and a customer name in column C. In column R, I have the following formula:
=$A1&" "&$C1
which yeilds a value something like "12345 Smith"

Now what makes this important is that in a directory on a network drive, another user has a series of workbooks with data I need to access. The path is:
T:\SALES\Equipment List\Current Equipment Lists

So I have an INDEX/MATCH formula which takes data from column B and retrieves a corresponding value. I have the formula working if I reference the exact path and file. But I need it to reference based on the file name that changes based on what row it's on. Something like this;

From:
=INDEX('T:\SALES\Equipment List\Current Equipment Lists\[12345 Smith.XLS]Equipment List'!$D$9:$D$1000,MATCH($B1,'T:\SALES\Equipment List\Current Equipment Lists\[12345 Smith.XLS]Equipment List'!$E$9:$E$1000,0))

To:
=INDEX("'T:\SALES\Equipment List\Current Equipment Lists\["&R1&".XLS]Equipment List'"!$D$9:$D$1000,MATCH($B1,"'T:\SALES\Equipment List\Current Equipment Lists\["&R1&"]Equipment List"'!$E$9:$E$1000,0))

Theoretically, I could put those whole path in a formula if that would be easier.
Any ideas?

Thanks,
Jim

immyjimmy · Jan 6, 2005

Bump...

How about something like:
=INDEX((($S1)&"!")&$D$9:$D$1000,MATCH($B1,(($S1)&"!")&$E$9:$E$1000,0))

where S1 is a concatenation of the entire path. The problem with this version is that I get a #VALUE error.

immyjimmy · Jan 6, 2005

Any ideas out there?

immyjimmy · Jan 6, 2005

Still nothing?

immyjimmy · Jan 6, 2005

Nobody? Is this one that hard?

Zack Barresse · Jan 6, 2005

Hey Jim,

You won't be able to use the entire path unless you use the INDIRECT.EXT function - which I believe is in the morefunc.xll add-in. And even that has it's limitations. You can reference the workbook (no path) with the INDIRECT. Also, even then, INDIRECT cannot handle 3-D array's. It's a pain, and MS should change it, as it's been mentioned quite a many times, but they haven't.

There are some workarounds that you can do though.

Method 1:

This UDF written by Harlan Grove (Pull) will open a seperate instance of a workbook in which to extract data from.

Option Explicit

Function Pull(xref As String) As Variant
 'inspired by Bob Phillips and Laurent Longre
 'but written by Harlan Grove
 '-----------------------------------------------------------------
 'Copyright (c) 2003 Harlan Grove.
 '
 'This code is free software; you can redistribute it and/or modify
 'it under the terms of the GNU General Public License as published
 'by the Free Software Foundation; either version 2 of the License,
 'or (at your option) any later version.
 '-----------------------------------------------------------------
 Dim xlapp As Object, xlwb As Workbook
 Dim b As String, r As Range, c As Range, n As Long
 Pull = Evaluate(xref)
 If CStr(Pull) = CStr(CVErr(xlErrRef)) Then
 On Error GoTo CleanUp 'immediate clean-up at this point
 Set xlapp = CreateObject("Excel.Application")
 Set xlwb = xlapp.Workbooks.Add 'needed by .ExecuteExcel4Macro
 On Error Resume Next 'now clean-up can wait
 n = InStr(InStr(1, xref, "]") + 1, xref, "!")
 b = Mid(xref, 1, n)
 Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))
 If r Is Nothing Then
 Pull = xlapp.ExecuteExcel4Macro(xref)
 Else
 For Each c In r
 c.Value = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
 Next c
 Pull = r.Value
 End If
CleanUp:
 If Not xlwb Is Nothing Then xlwb.Close 0
 If Not xlapp Is Nothing Then xlapp.Quit
 Set xlapp = Nothing
 End If
End Function


An explanation of it's use can be found here.

J-Walk also has a GetValue method for extracting data from closed workbooks found here.

Method 2:

Download/install the morefunc.xll add-in and make use of the INDIRECT.EXT function. †
Add-in found here.

Method 3:

Use an SQL.REQUEST, of which I am unfamiliar, but is laid out here. Instructions provided at site and with add-in.

Method 4:

Use an INDEX/MATCH combo (or othe suitable function desired) and type your formula in like this ...

Code:

="=INDEX('C:\Documents and Settings\Rob\Desktop\[12346 Jones.xls]Sheet1'!D9:E1000,match(r3,'C:\Documents and Settings\Rob\Desktop\[12346 Jones.xls]Sheet1'!E9:E1000),1)"

Note that I have changed the path for testing purposes. Copy the cell with the formula in it, then paste special --> values, making the value/formula static.
Next do a Find/Replace on that cell. Find = and Replace with = . (Sounds redundant, but it works.) The bad side to this is it takes up a lot of memory.

† Doesn't work with Defined Names of closed workbooks; and apparently does not work on all systems. Test first.

immyjimmy · Jan 7, 2005

Appearantly, doing this formula-magically is next to impossible. But in VBA, the following topic may be of use to future searchers:

http://www.mrexcel.com/board2/viewtopic.php?t=123811

HTH,
Jim

Zack Barresse · Jan 7, 2005

immyjimmy said:
Appearantly, doing this formula-magically is next to impossible. But in VBA, the following topic may be of use to future searchers:

http://www.mrexcel.com/board2/viewtopic.php?t=123811

HTH,
Jim

Hello Jim,

The formula option seems to work just fine for me. Fairly quick and painless also. Did you follow the steps exactly? Anyway, it is a non-vba working solution.

Note: credits of the above Methods do not go to me (I wish).

immyjimmy · Jan 14, 2005

Zack,

Yes, the formula works fine. But to use it, I have to configure it to a specific file. I couldn't "indirect()" it or "value()" it. What I wanted (and made a VBA solution for) was to concatenate the sales order number in cell A3 and the customer name in cell A1 to create the format that our purchasing agent uses for her equipment list workbooks. One to a customer order... I just wrote a macro to take the values of the cells and enter the formula with the variable name inserted in the right place. Now it looks up the data in the purchasers directory and returns the answer I needed. Namely whether or not a part number has been released, thereby flagging that engineering will be putting no more time into that portion of the project. The formula solutions I found either needed a non-variable name, or that the file I was looking in be open. Neither of these scenarios was acceptable and VBA works. So here's what I went with.

Sub To_Do_List_1()
Dim Sales_Number As String
Dim Sales_Name As String
Dim File_Name As String
Sales_Number = Sheets("Sheet1").Range("A3")
Sales_Name = Sheets("Sheet1").Range("A1")
File_Name = Sales_Number & " " & Sales_Name
Range("Q3").Select
ActiveCell.Value = "=INDEX('T:\SALES\Equipment List\Current Equipment Lists\[" & File_Name & ".XLS]Equipment List'!$D$9:$D$1000,MATCH($B2,'T:\SALES\Equipment List\Current Equipment Lists\[" & File_Name & ".XLS]Equipment List'!$E$9:$E$1000,0))"
'copy this formula along with others already in place...
Range("M4:Q4").Select
Selection.Copy
Range("M5:Q5").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste

Range("O5:P5").Select
Selection.Copy
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1:B1").Select
ActiveSheet.Paste

Range("M5:Q5").Select
ActiveCell.SpecialCells(xlLastCell).Select
Selection.End(xlToLeft).Select
ActiveCell.Offset(-1, 3).Range("A1:B1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1:B1").Select
ActiveSheet.Paste

Range("A3").Select
End Sub

It seems to work...

Thanks,
Jim

referencing a file from a value in a cell

immyjimmy

Active Member

Excel Facts

immyjimmy

Active Member

immyjimmy

Active Member

immyjimmy

Active Member

immyjimmy

Active Member

Zack Barresse

MrExcel MVP

immyjimmy

Active Member

Zack Barresse

MrExcel MVP

immyjimmy

Active Member

Similar threads

Forum statistics

Share this page

referencing a file from a value in a cell

Active Member

Excel Facts

Active Member

Active Member

Active Member

Active Member

MrExcel MVP

Active Member

MrExcel MVP

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock