Home |
Search |
Today's Posts |
#1
|
|||
|
|||
dynamic link
Is there a way to create a dynamic link?
For example if I had a list of Workbook names in column A, and a repective list of links in column B. Is there a way to create one formula that can be copied in Column B that would dynamically change according to Column A? Column A Column B Legal01 =C:\My Documents\[Legal01.xls]Sheet1!$A$1 Legal02 =C:\My Documents\[Legal02.xls]Sheet1!$A$1 Legal03 =C:\My Documents\[Legal03.xls]Sheet1!$A$1 |
#2
|
|||
|
|||
Itay wrote:
Is there a way to create a dynamic link? Yes, but the source workbooks all have to be open to get the results displayed. =INDIRECT("'C:\My Documents\[" & A1 & "]Sheet1'!$A$1") Usually it is more practical to create the specific formulas. For example if columns B to CZ contain references to different cells in the same workbook, copy them down from the first row and then select the new row and Edit / Replace / Legal01 (with) Legal02 / Replace All If there were very many rows it would be worth writing a macro to do it for each of the rows. It might go something like this: Sub SetUpFormulas() ' assumes data starts at A1, has header row and has filenames in column A ' and row 2 already contains correct formulas Dim I As Integer With Range("A1").CurrentRegion .Offset(1,1).Resize(.Rows.Count-1,.columns.count-1).FillDown For I=3 To .Rows.Count .Rows(I).Offset(,1).Replace Range("A2"), Range("A"&I), lookAt:=xlPart Next End With End Sub Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
removing automatic link | Excel Discussion (Misc queries) | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) | |||
How to Link named ranges from multiple Workbooks into a single Wo. | Excel Discussion (Misc queries) | |||
How do link to a remote worksheet using the path value in a field? | Links and Linking in Excel | |||
How do link to a remote field but use the path from a stored field | Excel Discussion (Misc queries) |