Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I use a cell value as the filename in an external link?
I want to set up a workbook with several values from linked workbooks. I
would like to enter a value in column A and then use that value as the filename in the rest of the cells that link to the external workbook. For example: I want to enter A122 into colum A - and have the cell in column B pick that value up and link to a cell in an external workbook with that name Obviously, the following function links correctly to the external file and returns the correct value: ='G:\Local files\Sample Tracking\[A122.xls]Sample Request'!$E$2 But, I do not want to have to change the syntax in a large number of cells everytime I add to the list. I only want to enter the value (A122) in the first cell and have the others retrieve linked values correctly. In other words, I want a dynamic, external link. Thanx in advance for any help. |
#2
|
|||
|
|||
You'd want to use the =indirect() worksheet function. But that doesn't work
with closed files. Harlan Grove wrote a UDF called that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ wattkisson wrote: I want to set up a workbook with several values from linked workbooks. I would like to enter a value in column A and then use that value as the filename in the rest of the cells that link to the external workbook. For example: I want to enter A122 into colum A - and have the cell in column B pick that value up and link to a cell in an external workbook with that name Obviously, the following function links correctly to the external file and returns the correct value: ='G:\Local files\Sample Tracking\[A122.xls]Sample Request'!$E$2 But, I do not want to have to change the syntax in a large number of cells everytime I add to the list. I only want to enter the value (A122) in the first cell and have the others retrieve linked values correctly. In other words, I want a dynamic, external link. Thanx in advance for any help. -- Dave Peterson |
#3
|
|||
|
|||
Wattkisson,
You could use the INDIRECT function, but the linked workbook must be open. Other than that, I think your only solution will be to create the formula with a macro. Often a project is split into separate workbooks (and separate worksheets), when it should be in fewer (or one) workbook. Consider that carefully; it might be what you need. -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "wattkisson" wrote in message ... I want to set up a workbook with several values from linked workbooks. I would like to enter a value in column A and then use that value as the filename in the rest of the cells that link to the external workbook. For example: I want to enter A122 into colum A - and have the cell in column B pick that value up and link to a cell in an external workbook with that name Obviously, the following function links correctly to the external file and returns the correct value: ='G:\Local files\Sample Tracking\[A122.xls]Sample Request'!$E$2 But, I do not want to have to change the syntax in a large number of cells everytime I add to the list. I only want to enter the value (A122) in the first cell and have the others retrieve linked values correctly. In other words, I want a dynamic, external link. Thanx in advance for any help. |
#4
|
|||
|
|||
following is a code i am using in one of my spreadsheet to do similar thing. the only difference might the fact that i am retreiving same cell (A3) form each file. Code: -------------------- Private Sub Worksheet_Change(ByVal Target As Range) Dim flnm As String Application.EnableEvents = False If Target.Column = 1 And IsEmpty(Target.Value) = False Then flnm = "C:\Documents and Settings\kapatel\Desktop\" & Target.Value & ".xls" If Dir(flnm) < "" Then Me.Cells(Target.Row, 2).Formula = "='C:\Documents and Settings\kapatel\Desktop\[" & Target.Value & ".xls]Details'!$A$3" Else Me.Cells(Target.Row, 2).Value = "File not found." End If ElseIf IsEmpty(Target.Value) = True Then Me.Cells(Target.Row, 2).Value = "File not found." End If Application.EnableEvents = True End Sub -------------------- Hope this helped Keyur -- Keyur ------------------------------------------------------------------------ Keyur's Profile: http://www.excelforum.com/member.php...fo&userid=7786 View this thread: http://www.excelforum.com/showthread...hreadid=385705 |
#5
|
|||
|
|||
Thanks. INDIRECT did the trick. Now I will try to get around the open
workbook updating issues as you suggested. "Dave Peterson" wrote: You'd want to use the =indirect() worksheet function. But that doesn't work with closed files. Harlan Grove wrote a UDF called that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ wattkisson wrote: I want to set up a workbook with several values from linked workbooks. I would like to enter a value in column A and then use that value as the filename in the rest of the cells that link to the external workbook. For example: I want to enter A122 into colum A - and have the cell in column B pick that value up and link to a cell in an external workbook with that name Obviously, the following function links correctly to the external file and returns the correct value: ='G:\Local files\Sample Tracking\[A122.xls]Sample Request'!$E$2 But, I do not want to have to change the syntax in a large number of cells everytime I add to the list. I only want to enter the value (A122) in the first cell and have the others retrieve linked values correctly. In other words, I want a dynamic, external link. Thanx in advance for any help. -- Dave Peterson |
#6
|
|||
|
|||
Thanks. INDIRECT did the trick.
"Keyur" wrote: following is a code i am using in one of my spreadsheet to do similar thing. the only difference might the fact that i am retreiving same cell (A3) form each file. Code: -------------------- Private Sub Worksheet_Change(ByVal Target As Range) Dim flnm As String Application.EnableEvents = False If Target.Column = 1 And IsEmpty(Target.Value) = False Then flnm = "C:\Documents and Settings\kapatel\Desktop\" & Target.Value & ".xls" If Dir(flnm) < "" Then Me.Cells(Target.Row, 2).Formula = "='C:\Documents and Settings\kapatel\Desktop\[" & Target.Value & ".xls]Details'!$A$3" Else Me.Cells(Target.Row, 2).Value = "File not found." End If ElseIf IsEmpty(Target.Value) = True Then Me.Cells(Target.Row, 2).Value = "File not found." End If Application.EnableEvents = True End Sub -------------------- Hope this helped Keyur -- Keyur ------------------------------------------------------------------------ Keyur's Profile: http://www.excelforum.com/member.php...fo&userid=7786 View this thread: http://www.excelforum.com/showthread...hreadid=385705 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possible Lookup Table | Excel Worksheet Functions | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
GET.CELL | Excel Worksheet Functions | |||
Filename in a cell | Excel Worksheet Functions | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |