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
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?
RE: How to include contents of a cell in a link?
Here are some examples from simple to complex.
First:
CODE
CODE
CODE
CODE
CODE
Cell P5
CODE
CODE
CODE
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?
CODE
'
' 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
RE: How to include contents of a cell in a link?
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?
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?
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?
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?
Damned pity. Particularly since it used to work.
RE: How to include contents of a cell in a link?
CODE
'
' 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
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?
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?
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