×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How to include contents of a cell in a link?

How to include contents of a cell in a link?

How to include contents of a cell in a link?

(OP)
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

RE: How to include contents of a cell in a link?

Look at the INDIRECT function.

RE: How to include contents of a cell in a link?

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:

CODE

=INDIRECT($P$10)
Cell P10 contains:

CODE

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

CODE

=VLOOKUP(WO!$F$31,INDIRECT($P$13),2,0)
Cell P13 contains:

CODE

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

CODE

=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

CODE

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

CODE

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

CODE

="'[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.

RE: How to include contents of a cell in a link?

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.

CODE

Function GetValue(Path, File, Sheet, Ref)
'
'  Retrieves a value from a closed workbook.
'
'  Code obtained from
'  http://j-walk.com/ss/excel/tips/tip82.htm
'  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.

RE: How to include contents of a cell in a link?

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?

RE: How to include contents of a cell in a link?

(OP)
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  

RE: How to include contents of a cell in a link?

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.

RE: How to include contents of a cell in a link?

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:
="='G:\(folder name)\["&A1&"]Sheet1'!$D$3"
Then, as code for your Update button, you only need
Sub UpdateButton_Click()
    Range("D1").Formula = Range("B1").Value
    Range("D2").Formula = Range("B2").Value
End Sub

Cheers,
Joerd

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

RE: How to include contents of a cell in a link?

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.

RE: How to include contents of a cell in a link?

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

CODE

Function GetValue(path, file, sheet, ref)
'
'  Retrieves a value from a closed workbook.
'
'  Code obtained from
'  http://j-walk.com/ss/excel/tips/tip82.htm
'  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.

RE: How to include contents of a cell in a link?

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.

RE: How to include contents of a cell in a link?

(OP)
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   

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources