Alrighty. The indirect doesn't like the path (doesn't mind it if the file's open, but isn't responsive when closed, defeating the purpose), which the code above is based on, so I'm going to rephrase this.
If you use an indirect formula method, the target file has to be open. To open the file with a vba procedure, use the following code with Chris' formula. Right-click on the sheet with your Data Validation list, click 'view code' and paste the following code:<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
'Macro recorded by Nate
Dim o As Integer, s As Integer
Dim z As String
If Target.Address = "$A$1" Then
o = InStr([b1].Formula, "[")
s = InStr([b1].Formula, "]")
ChDrive ("C") 'hardcode your target drive letter
ChDir ("C:tempxltest") 'hardcode your target file path
z = Application.Substitute(Mid([b1].Formula, _
o + 1, s - 1 - o), ".xls", "") & ".xls"
Workbooks.Open [a1].Value & Application.Substitute(z, _
"""&$A$1&""", "")
End If
End Sub</pre>
However, you can set this up with a pure vba solution that will allow you to update your information without having to open the target workbook.
Setting up the link is the easy part. Open your master file and one of the monthly files (e.g., JanData.xls). Now in the master click +, then move to the JanData.xls, click on the cell of choice file and press enter. You've now established your link. Remove the $ signs to remove absolute addressing.
Now, if you right click on the sheet with your validation list, click view code, paste the following code in:<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
'Macro recorded by Nate
Dim Worksheet As Worksheet, cl As Range, frm As String
Dim z As String, o As Integer, s As Integer, FrmCls As Range
If Target.Address = "$A$1" Then
For Each Worksheet In ThisWorkbook.Worksheets
Set FrmCls = Nothing
On Error Resume Next
Set FrmCls = Worksheet.[a1].SpecialCells(xlFormulas)
If FrmCls Is Nothing Then GoTo 1
For Each cl In FrmCls
frm = cl.Formula
If InStr(frm, "[") Then
o = InStr(cl.Formula, "[")
s = InStr(cl.Formula, "]")
z = Mid(cl.Formula, o + 1, s - o)
cl.Replace What:=z, Replacement:=[a1].Value & "Data.xls]", _
MatchCase:=False, lookat:=xlPart
End If
Next cl
1:
Next Worksheet
End If
End Sub</pre>
If you change Jan to Feb in cell a1, every link your workbook that had JanData.xls will be updated to FebData.xls
However you want to work it, I think the 2nd's a little easier to handle from an end-user perspective.
Hope this helps.
_________________
Cheers,<font size=+2><font color="red">
Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-05-13 14:21 
