How to include contents of a cell in a link? 1

[griffengm]

In cell A1 I have a part name e.g. "XXXX". In cell a2 I have another name, "ZZZZ". These also happen to be the names of some workbooks that I wish to reference. In cell d1, I want to reference cell D3 in workbook "XXXX.xls" & in cell D2 reference cell D3 in workbook "ZZZZ.xls".
Is there a way to set up a generic formula in column D that will read the name in column A and use it to reference the appropriate workbook.
I see it looking something like this: "=G:\(folder name)\[(partname from column A).xls]$D$3 where G is drive designator,& folder name is same for all part name workbooks. The formula in d1 will return the value in G:\(folder name)\[XXXX.xls]$D$3, and in d2 returns the value in G:\(folder name)\[ZZZZ.xls]$D$3.
Macro? Text string function?

Griffy

 

[MintJulep]

Look at the INDIRECT function.

 

[bpeirson]

I managed to do this but the syntax gets tricky while using the INDIRECT function. I have taken to copying a cell that works and then modifying the text to what I need in the new cell. I think that the use of the single quote at the beginning of the reference is what gives me trouble when typing into a new cell what I think will work.

Here are some examples from simple to complex.

First:

=INDIRECT($P$10)

Cell P10 contains:

''[Database.xls]MU'!$D$1

Second:

=VLOOKUP(WO!$F$31,INDIRECT($P$13),2,0)

Cell P13 contains:

''[^Customer Data.xls]Project Info'!$C$2:$E$200

Last:

=INDEX(INDIRECT($P$5),MATCH(D16,INDIRECT($Q$5),0),MATCH("Billing weight",INDIRECT($R$5),0))

Cells P5, Q5 and R5 contain:

Cell P5

="'[Database.xls]"&K5&"'!$A$1:$AZ$7000"

Cell Q5

="'[Database.xls]"&K5&"'!$A:$A"

Cell R5

="'[Database.xls]"&K5&"'!$1:$1"

In the first and second examples cells P10 and P13 start with 2 single quote marks, not a single regular quote mark. When I look at the cell only one of the single quotes appears unless I look to the formula bar which shows both single quotes.

In the last example cell K5 contains the worksheet name in the Database.xls spreadsheet which I want to reference.

In all examples the spreadsheet being refered to must be open for these formulae to work. I have it open but minimized, you can have it open but hidden, both ways work.

Good luck and keep trying, it does work if you get it right.

 

[Denial]

This can be done using a user-defined function that itself uses an old Excel4 capability. See code below, which comes from that living legend John Walkenbach.

Function GetValue(Path, File, Sheet, Ref)
'
'  Retrieves a value from a closed workbook.
'
'  Code obtained from
'  [URL unfurl="true"]http://j-walk.com/ss/excel/tips/tip82.htm[/URL]
'  in September 2002.
'
Dim arg As String
'  Make sure the file exists
If Right(Path, 1) <> "\" Then Path = Path & "\"
If Right(LCase(File), 4) <> ".xls" Then File = File & ".xls"
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

You might need to make some minor changes to meet your exact needs.

 

[bpeirson]

Denial, is this function currently working for you. I copy pasted it but I get "#Value!" after I type in a value for both the Path and the Filename. If I leave either one of these blank the function returns "File Not Found" as expected.

Is the Excel4 capability still accessable?

Is the syntax for the Excel4Macro still valid in the newer versions of Excel?

 

[griffengm]

The files from which data is retrieved are most likely closed. I would rather not need to open them as the list could be rather lengthy.
I will take a stab at setting up the function Denial sent but if it also requires files open it will be unwieldy.
BPierson's method of copying a formula that works and then modifying text in it is the method currently in use but again, it is cumbersome and time consuming when the list has many items.
Driving all of this is a desire for more detailed information on processes from the early experimental stages of a new product development. We use MRP software for production but need to track the effects of design changes on overall costs as early as possible, i.e. before MRP can be used.

Griffy

 

[Denial]

Bpeirson,

You are right. My function no longer functions.

I developed it (ie I grabbed it) in 2002 when I was still using Excel 97, and it worked fine then. Now that I try it in Excel 2002 I am getting the same failure that you report.

The web site from which I got it still offers it. However there is a warning on the page which I do not remember from my previous foray: "You cannot use this function in a worksheet formula". With this warning in mind I did a few tests. I can successfully call the function from a VBA subroutine, and store the result in a variable. However I cannot call the function from another VBA function.

My apologies. I should have tested it out with E2002 before posting it. I will do a bit more experimenting and see if I can find a way around this new blockage.

 

[joerd]

You could set it up with an "Update" button - or a Worksheet_Change event. You need a "helper" column to set up the appropriate reference as a string (or use the first part of Jwalk's function to generate arg).
For example, in cell [B1] you would enter the following formula:
[tt]="='G:\(folder name)\["&A1&"]Sheet1'!$D$3"[/tt]
Then, as code for your Update button, you only need
[tt]Sub UpdateButton_Click()
Range("D1").Formula = Range("B1").Value
Range("D2").Formula = Range("B2").Value
End Sub[/tt]

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Denial]

The general consensus out there in Googleland seems to be that the "GetValue" function cannot be made to work when it is used, either directly or indirectly, from a worksheet formula.

Damned pity. Particularly since it used to work.

 

[joerd]

I played around a bit, you can use Evaluate instead of ExecuteExcel4Macro. The code becomes like this:

Function GetValue(path, file, sheet, ref)
'
'  Retrieves a value from a closed workbook.
'
'  Code obtained from
'  [URL unfurl="true"]http://j-walk.com/ss/excel/tips/tip82.htm[/URL]
'  in September 2002.
'  Modified to use Evaluate instead of ExecuteExcel4Macro

Dim arg As String
'  Make sure the file exists
Application.Volatile
If Right(path, 1) <> "\" Then path = path & "\"
If Right(LCase(file), 4) <> ".xls" Then file = file & ".xls"
If Dir(path & file) = "" Then
    GetValue = "File Not Found"
    Exit Function
End If
'  Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & ref
GetValue = Evaluate(arg)
End Function

Hope this works for you.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Denial]

Joerd,

Your modification seems to work only if the source spreadsheet is open. Back in Excel97 days, the "ExecuteExcel4Macro" approach worked with a closed spreadsheet (hence the need for the "path" argument). Thus you have improved things, for which thanks, but perfection still eludes us.

 

[griffengm]

A note to let you know I appreciate the help and hope we can follow this question through to resolution. It is necessary that the solution be able to work with closed worksheets.
Periodicly, through the course of development, I need to open various workbooks to update some labor as design changes but once these are created there is minimal need to update them. The biggest changes occur in materials costs. The summary sheet pulls the material identifier from the labor sheet, a cost factor from an item cost database and calculates the part material cost.
Thanks again.
Griffy